Update a column with data from another table after form submit.

[AlexChe]

Hello,

I am building an app to manage employee holidays. 

I have two tables one Employee with column Pending Holiday which is a Decimal Type, and table Holiday with column Days Left(Decimal) that gets populated with the data of Pending Holiday minus the Days Taken(Decimal).

I would like to update the Pending Holiday Column in the Employee table, every time an employee submits a holiday request with the data from the Days Left column which is in the Holiday table.

Table Holiday Columns:

Table Employee Columns:

When putting Holiday[Days Left] in the Formula input of Pending Holiday I get the following error message :

I would like to avoid having to create an Action Button, and have the changes be made dynamically.

[Hussein_Osseily]

Try this maybe ? 

SUM(
  SELECT(
    HOLIDAY[DAYS LEFT],
      ([EMPLOYEE] = [_THISROW].[NAME]), 
  )
)

[AlexChe]

Thank you for your reply.

Unfortunately it did not work. 

The Pending Holiday column still does not update dynamically the data from Days Left after submitting a form.

A few precision on the functionality.

When a manager creates an employee in the App, it enters manually the numbers of Pending Holiday.

When the employee creates a Holiday Request, it enters the date From and To, which then fills in the Days Taken column. Then the Days Left gets automatically filled in with the value from Pending Holiday minus Days Taken.

[Hussein_Osseily]

when you test the expression does it return the correct value ? 
i think if am not mistaken , you might need to create and action with that expression and add it in the form behavior so that every time it's updated the days would be updated too 

incase you need help with setting this up , i can assist you 

[AlexChe]

Hello,

It only returns 0 as a value.

I will check with the action on a form submit, thank you.

[Hussein_Osseily]

Action on form submit worked for me (i did it in a test app just like yours) 

[AlexChe]

I am fairly new to this and unsure how to apply an action on form submit.

This is what I put:

This is the preview:

It sets the Pending Holiday as 0.00.

Any insight on what I am obviously missing?

[Suvrutt_Gurjar]

Please go through the documents and sample app shared. You need to set two actions for reference actions.

For form save, you can set the event action in the following setting of the form view.

[Suvrutt_Gurjar]

The real columns update only when the corresponding row is edited by the user or through actions or automation.

Please create reference actions to update the [Pending Holiday] column in Employees table whenever the user saves the Holidays table form. Please take a look at event actions to understand how you can do so when a form is saved.

View events - AppSheet Help

Please take a look at the section "Data: execute an action on a set of rows" in the article below

Actions: The Essentials - AppSheet Help

Please take a look at the sample app "reference actions" in teh sample app list below.

Get started by using the feature samples - AppSheet Help

[AlexChe · Accepted solution]

Hello everyone, with the help of @Hussein_Osseily , I managed to get what I wanted.

This is a rundown on how it is installed.

In the DaysLeft Column add this formula:

[ALLOWED DAYS]-SUM(
SELECT(
HOLIDAY[DAYS TAKEN],
([EMPLOYEE] = [_THISROW].[EMPLOYEE])
)
)

In the AllowedDays add this Formula:
LOOKUP([_ThisRow].[EMPLOYEE], "EMPLOYEE", "NAME", "Allowed Holidays")

Then in Behaviour we will add for the employee table these settings:

Formula:
IF(
IN([NAME],HOLIDAY[EMPLOYEE]),
([Allowed Holidays]-SUM(SELECT(
HOLIDAY[DAYS TAKEN],
([EMPLOYEE] = [_THISROW].[NAME]),
))),[Allowed Holidays])

For the Holiday Table:

Formula: FILTER("holiday", ([employee] = [_THISROW].[EMPLOYEE]))

Formula: UNIQUEID()

Last on Form Submission for Holiday_Form add this part in UX:

Again thank you for your help everyone.