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
Not sure how your tables are linked but it seems like you could just make Courses[sector] equal to the Base db[ID].[Sector]
1 Like
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
See this post
https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Automatically-update-app-formulas/td-p/398851
1 Like
HI
Thank you, I saw the post, but I really don’t know where to start, I’m not very experienced with Appsheet
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!))
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!
1 Like
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.
1 Like
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.
I’m sorry, do you think I couldn’t do it with an action button?
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.
1 Like
ok I’ll try with the Bot I’ve never used 
I update you
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
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.
I tried this but it doesn’t update anything, I just can’t get there
SELECT(ELENCO_TRAINING[Area],[Area]=[_thisrow].[Area])
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])
1 Like
HI
I have tried nothing but it only updates the Area column in the “Staff” table in the “Trainibg list” table remains unchanged
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.
1 Like
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