row reference

[Cannone_Cannone]

Hi everyone!!!
small problem that I can't solve.
I have two "Data base" tables where there are staff data "(Name, Surname, ID, Sector) and a "Courses" table.
In the "Courses" table I add the courses taken by the staff every day, it may happen that the staff changes sector for a period, therefore by changing the Sector column in the "base db" table I would like the sector in the "Courses" table to also change on all Is the courses already completed and registered possible? I tried but I can't. I hope I explained myself

[Alyssa_QREW · Accepted solution]

To me this sounds like you need a virtual column rather than a static column. The virtual column is not stored in the spreadsheet and is constantly updated (not always 100% instant but in this case will probably be instant).

The advantage to the virtual column is that you don't have to go back an update who knows how many courses when you change a users area, AppSheet will do it automatically. Disadvantages are that it can be inefficient (this is not an inefficient formula though so probably not a big deal in this case) because AppSheet is constantly looking at what the Virtual Column should be, it also will change old records (which can cause issues but it sounds like you want in this case anyway).

You can add a virtual column by hitting the plus icon in the top right hand corner of the courses table in the data tab. 

If you cant do a virtual column for some reason I would make a bot that runs on updates the users table with this condition [_thisrow_before].[area]<>[_thisrow_after].[area] 

This condition will make the bot run anytime that you change the area on the users table. Then you can do a Step in the bot that is "Run a Data Action". Then select "Execute An Action on a Set of Rows". You will run the action from the Users table onto the Courses Table. Then in the referenced rows you will have to select all a list of all the course for that user (may be able to use the system generated related column to get the related courses. If you want to exclude specific courses you would need to use a SELECT ). Then you can link an action that updates the area (you will have to remove the formula to change the area in an action but you could do an initial value with reset on edit checked to get a similar function as a formula).

This second option could update a lot of rows if you change the area often so I would not recommend it unless there is a good reason that you can't use a virtual column.

Please let me know if any of that doesn't make sense!

[Alyssa_QREW · Accepted solution]

Also I would recommend staying away from spreadsheet formulas mixed with AppSheet formulas. While you can do them, they can cause issues down the road. If there are ways to accomplish in AppSheet I think those are almost always better than using spreadsheet formulas. 

[Alyssa_QREW · Accepted solution]

You could Execute an Action on a Set of Rows in an Action rather than a bot  if you would prefer. You could either have an action you press or put it on Form Save.  It may be better to use a bot if you are updating a lot of rows though because actions will add a sync for every row you update which can cause issues with people having to wait for excess syncs. 

[Alyssa_QREW · Accepted solution]

So if you want changes to the Staff table to Update the Training List Table then you would need to pick the staff table in the Event (not sure which one tbl_organico_storico is but it needs to be the staff table there). The update part looks good, and then the portion of the condition I can see looks good too. 

Then Click Add A Step. There you will select "Create A Custom Step" Then click on the step and change "Run a Task" to "Run A Data Action". Then select "Run action on rows" on the right hand side. Then choose the Training list table in referenced table there. 

In Referenced rows you will need to select the ID of all the training list rows you want to update. Something like SELECT(Training List[Key],[Employee ID]=[_thisrow].[Key]) may work (with your column names rather than the general ones I put in). Then you will have to make an action on the training list table that updates the area from the staff table and then link that action in the referenced action field. 

[Alyssa_QREW · Accepted solution]

I would recommend starting by looking at the automation monitor. You can access this by clicking the Monitor icon to the right of the bot name. If your bot is not in the automation monitor at all then there is likely an issue with your event. If it is in the monitor then it is likely an issue with the step, and there may even be an error to help you narrow down the issue. If you send me screenshots of the event, step, and the action triggered through your action on a set of rows, and the automation monitor I would be happy to take a look. 

[lynchk21]

Not sure how your tables are linked but it seems like you could just make Courses[sector] equal to the Base db[ID].[Sector]

[Cannone_Cannone]

Hi, sorry, I didn't understand

the tables are linked like this

[Cannone_Cannone]

I tried like this but it doesn't work, what I would like to do is that when I change the value in the "AREA" column of the "tbl_organico" table you also change the value in the "training list" table, "Area" column, overwriting the old data

[TeeSee1]

See this post 

https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Automatically-update-app-formulas/td-p/398851

[Cannone_Cannone]

HI
Thank you, I saw the post, but I really don't know where to start, I'm not very experienced with Appsheet

[Cannone_Cannone]

Hi everyone
this is the formula I use in excel to do what I asked. but I can't do it in my App, I could leave the formula in the sheet but I don't want to make the application heavier

=IFERROR(VLOOKUP([@[Id Employee ]];Tbl_Organico_Storico!C:P;14;FALSE);IFERROR(VLOOKUP(INT([@[Id Employee ]]);Tbl_Organico_Storico!C:P;14;FALSE);#REF!))

[Alyssa_QREW · Accepted solution]

To me this sounds like you need a virtual column rather than a static column. The virtual column is not stored in the spreadsheet and is constantly updated (not always 100% instant but in this case will probably be instant).

The advantage to the virtual column is that you don't have to go back an update who knows how many courses when you change a users area, AppSheet will do it automatically. Disadvantages are that it can be inefficient (this is not an inefficient formula though so probably not a big deal in this case) because AppSheet is constantly looking at what the Virtual Column should be, it also will change old records (which can cause issues but it sounds like you want in this case anyway).

You can add a virtual column by hitting the plus icon in the top right hand corner of the courses table in the data tab. 

If you cant do a virtual column for some reason I would make a bot that runs on updates the users table with this condition [_thisrow_before].[area]<>[_thisrow_after].[area] 

This condition will make the bot run anytime that you change the area on the users table. Then you can do a Step in the bot that is "Run a Data Action". Then select "Execute An Action on a Set of Rows". You will run the action from the Users table onto the Courses Table. Then in the referenced rows you will have to select all a list of all the course for that user (may be able to use the system generated related column to get the related courses. If you want to exclude specific courses you would need to use a SELECT ). Then you can link an action that updates the area (you will have to remove the formula to change the area in an action but you could do an initial value with reset on edit checked to get a similar function as a formula).

This second option could update a lot of rows if you change the area often so I would not recommend it unless there is a good reason that you can't use a virtual column.

Please let me know if any of that doesn't make sense!

[Alyssa_QREW · Accepted solution]

Also I would recommend staying away from spreadsheet formulas mixed with AppSheet formulas. While you can do them, they can cause issues down the road. If there are ways to accomplish in AppSheet I think those are almost always better than using spreadsheet formulas. 

[Cannone_Cannone]

Hi, thank you for your interest, the problem is that I cannot create the virtual column because I need the updated data in the Excel sheet where I have connected a pivot table that also reads the "AREA" column.
So I don't know which way to go, the idea of keeping the formula in the sheet is to be discarded as you suggested.

[Cannone_Cannone]

I'm sorry, do you think I couldn't do it with an action button?

[Alyssa_QREW · Accepted solution]

You could Execute an Action on a Set of Rows in an Action rather than a bot  if you would prefer. You could either have an action you press or put it on Form Save.  It may be better to use a bot if you are updating a lot of rows though because actions will add a sync for every row you update which can cause issues with people having to wait for excess syncs. 

[Cannone_Cannone]

ok I'll try with the Bot I've never used 🙂

I update you

[Cannone_Cannone]

Hi, I tried with the bot, but I should update the Area from the "Staff" table and the changes should be made on the "Training list" table

[Alyssa_QREW · Accepted solution]

So if you want changes to the Staff table to Update the Training List Table then you would need to pick the staff table in the Event (not sure which one tbl_organico_storico is but it needs to be the staff table there). The update part looks good, and then the portion of the condition I can see looks good too. 

Then Click Add A Step. There you will select "Create A Custom Step" Then click on the step and change "Run a Task" to "Run A Data Action". Then select "Run action on rows" on the right hand side. Then choose the Training list table in referenced table there. 

In Referenced rows you will need to select the ID of all the training list rows you want to update. Something like SELECT(Training List[Key],[Employee ID]=[_thisrow].[Key]) may work (with your column names rather than the general ones I put in). Then you will have to make an action on the training list table that updates the area from the staff table and then link that action in the referenced action field. 

[Cannone_Cannone]

I tried this but it doesn't update anything, I just can't get there

SELECT(ELENCO_TRAINING[Area],[Area]=[_thisrow].[Area])

[Alyssa_QREW]

You need to have the Key of elenco training in the first square brackets not the area. Like this:

SELECT(ELENCO_TRAINING[Key],[Area]=[_thisrow].[Area])

[Cannone_Cannone]

HI

I have tried nothing but it only updates the Area column in the "Staff" table in the "Trainibg list" table remains unchanged

[Alyssa_QREW · Accepted solution]

I would recommend starting by looking at the automation monitor. You can access this by clicking the Monitor icon to the right of the bot name. If your bot is not in the automation monitor at all then there is likely an issue with your event. If it is in the monitor then it is likely an issue with the step, and there may even be an error to help you narrow down the issue. If you send me screenshots of the event, step, and the action triggered through your action on a set of rows, and the automation monitor I would be happy to take a look. 

[Cannone_Cannone]

hi thanks for your help, here is the screen, I didn't create an action button, I don't really understand where I should start from thanks

[Alyssa_QREW]

So the screen you sent shows me that the issue is with the Step Portion of the bot, not the event. 

Because it says complete, that means that the step has all valid formulas but that they just are not functioning correctly. 

You sent this screenshot before:

Can you show me what is now in the Referenced Rows Box. Then the action I was referring to is the "Aggiungi in Training Record" action that you put in the Referenced Action Box. That should be updating the area on each of the training rows to match the staff table. Can you show me what that looks like as well?

[Cannone_Cannone]

So now something strange happens, if I change the value of the AREA column in the "Staff" table, an entire page of data populates in the "Training Record" sheet but I don't want this, I want the value in the Area column to change Training List sheet, but I can't choose it in the Bot

Let me explain better if I now modify the department of an employee in the STAFF table, now with the Bot active, I paste all the data of the employees who are in that department into the training record sheet

[Cannone_Cannone]

I have attached the screen of the action to keep track of all the changes made, which end up in the Training Record sheet which is a separate sheet

[Cannone_Cannone]

Maybe I'm doing something wrong here?

[Cannone_Cannone]

this is the screen of the action that sends me the changes made in the separate TRAining Record sheet

[Alyssa_QREW · Accepted solution]

So the Referenced Action Section lets you choose from the existing actions that exist on that table in the Behavior Menu. You will just need to add a new action there. You should see an add button at the top. If you click that you can add an action with "For a Record of this table" of "Elenco_training". Then "Do This" Should be "Data:set the values of some columns in this row". Then in "Set These columns" you will need to choose the Area column and put a formula of [id employee].[area]. 

Then you will go back to the bot and find the new action that you made and use that in referenced action rather than the Aggiungi in training record action.  

[Cannone_Cannone]

So I created an action as you suggested called "Update Area" and now I can also see it in the Bot, but the formula I insert into the action gives me this error

[Alyssa_QREW]

You will need to go to the Data tab and make Id Employee a reference to the staff table. It looks like in the original screenshot of the data table that you uploaded that how you had it set up. Sorry I did not notice that it had changed. 

Also I would recommend making sure that the Key column is not the employee for the training table. You likely want to check the Key check box on the Key column in the training table. If you leave the key box checked for the the id employee column you will only be able to add one training record per employee.

Here is the screenshot of what it was, I would recommend changing it back to this. Note, you do not need to add the formulas back so I did include them in the screenshot in purpose. 

[Cannone_Cannone]

😭

😭
I don't believe it

[Cannone_Cannone]

I did everything as you suggested

[Cannone_Cannone]

Screen

[Cannone_Cannone]

Hi, the action I created works if I start it individually for each row of the "Workout List" table, I can't do it with the bot, I think the formula isn't right, what do you think?

SELECT(ELENCO_TRAINING[key],[Area]=[_thisrow].[Area])
Grazie

[Cannone_Cannone]

Hi, do you happen to have another suggestion?