How do I update a field based on adding a row on another table?

AppSheet AppSheet Q&A
, ,
1

Hi all! Hopefully someone can assist me with my problem.

I am creating an app that helps track items found in a game in order to complete various missions. These are the tables I am using:

MISSIONS - This is the main table where I record various missions, ie Mission Name, Date Added, etc

MISSION ITEMS - Table where each record is a unique item, and has fields such as qty required, qty found, qty pending. When shown as a child table on the MISSION view, the qty for each item seen is the qty pending field, essentially showing the remaining items needed.

ITEM - Just a reference table of each unique item in the game

NEEDED ITEMS - Since some missions have overlapping items/qty, this table records the highest qty of each item, giving me an overview of all the items I need.

GAME LOG - Each record is a game played

GAME LOG ITEMS - each record is an item/qty recovered from the game. Currently, it also updates the NEEDED ITEMS table by first capturing the total needed at time of record creation, deleting row on NEEDED ITEMS, and adding a new row with current total, until qty of item on NEEDED ITEMS is 0.

I need some sort of action or automation when a record is created on GAME LOG ITEMS where it will update the qty found field on the MISSION ITEMS table, and then refresh the MISSIONS table so when a mission is viewed, it shows the updated quantities need for each item. I have attempted to create an action to do this, but can only see how to add/delete rows, and not edit particular columns within a row. I saw that maybe input() might be an avenue, but I do not really understand how it is used, and how it may be applied to my project.

Any help or suggestions regarding this problem will be very much appreciated!

2

It sounds you need an action called “Execute an action on a set of rows”. It means It finds the correct row in a table (in the same or different table) and then it triggers the action#2 in that row. That action will then update the column you need.

3

Hi!

Thanks for that advice, I think I have an idea of where to go. My next problem is being able to grab the data for the [Qty] column on the Game Log Items tables and inserting it into the [Found QTY] column on the Mission Items table.

For the bot running on the Game Log Items, I created an action called “Trigger Update on Mission Items” using the Execute an action on a set of rows. I then created an action on the Mission Items table called “Update Found Qty”.

My thought was to do a lookup using a MAXROW() on the _rownumber, as I thought that would bring up the newest create record on Game Log Items, but for some reason it does not fill in the number on [Found Qty]. Just to make sure it was working, I tested it with just a whole number and not an expression, and it did everything it needed to, so it is just an issue of grabbing that particular number from Game Log Items.

LOOKUP(
  MAXROW("Game Log Items",  "_RowNumber",  ([Item] = [_THISROW].[Item])),
  "Game Log Items",
  "_RowNumber",
  "Qty"
)

Any idea of what I am doing wrong, or do you have a better suggestion for grabbing the data?

4

So erhalte ich einen Spaltenwert aus der letzten Zeile der Tabelle.

LOOKUP(
  MAX(My Table[_ROWNUMBER]),
  "My Table",
  "_ROWNUMBER",
  "Wanted Column"
)

Ersetzen Sie durch den Namen der Tabelle, aus der der Spaltenwert stammen soll. und mit dem Namen der Spalte, deren Wert Sie wünschen.

5

Is the rownumber your key column in the Game Log Items table? If it’s not, then that is at least one reason. The result of MAXROW() is always the key value and you should change the “_rownumber” to your key column inside of the LOOKUP().

6

Thanks for the response. I can see what you meant. I ended up using MAX as opposed to MAXROW and it worked