Insert Data into Table B from Table A ONLY if a unique key is not present

[Matthew_Ronan]

Lay of the land:
I have an app for users to log exercise activities for a particular challenge they are enrolled in. The app consists of the following tables:
Challenges
Challenge_Exercises
Challenge_Users
Challenge_Progress
Exercises
Users
User_Stats
Exercise Entry

There are references between some of the tables:
User Stats > Users on User ID
Challenge_Exercises > Exercises on Exercise ID
Challenge_Exercises > Challenges on Challenge ID
Challenge_Users > Users on User ID
Challenge_Users > Challenges on Challenge ID

All of the activities get logged to the Exercise_Entry table which is currently set up with the Challenge ID, User ID, and Exercise ID (among other) fields.

I need to get the Challenge ID, User ID, and Exercise ID copied to the Challenge_Progress table.
The catch, I only want ONE UNIQUE record in the table for a given Challenge ID, User ID, Exercise ID.

The challenge_user table is designed to store total complete reps by a given Challenge ID, User ID, and Exercise ID. This will drive some charting and other information displayed on the dashboard.

I am struggling to figure out how to perform the lookup with all of those IDs against that table to determine if that “key” exists. FYI I do have a column on that table to store that “key”

let me know if you need more information or detail

[Steve · Accepted solution]

Matthew_Ronan:

I am struggling to figure out how to perform the lookup with all of those IDs against that table to determine if that “key” exists.

The following expression can be used from the Exercise_Entry table to determine if the (User ID, Challenge ID, Execise ID) combo of a given row of that table already exists in a row of the Challenge_Progress table:

ISBLANK(
  FILTER(
    "Challenge_Progress",
    AND(
      ([_THISROW].[User ID] = [User ID]),
      ([_THISROW].[Challenge ID] = [Challenge ID]),
      ([_THISROW].[Exercise ID] = [Exercise ID])
    )
  )
  - LIST([_THISROW])
)

[sunil_prajapati]

Hello to everyone

in this same way I want to trigger action of add new row in another table Via workflow.

data from attendance and target table is salary

condition is it will only trigger if employee month and year data are not available in salary table,

so by this ways it will trigger only once in month.

i tried this

ISBLANK(
FILTER(
“salary”,
AND(
([_THISROW].[MONTH] = [MONTH]),
([_THISROW].[F.YEAR] = [F.YEAR]),
([_THISROW].[OPERATOR] = [OPERATOR NAME])
)
))

success

[sunil_prajapati]

UPDATING MY STATUS

its work perfectly

i have a problem in the month column I change it from date to text type same as the attendance table.

solve, one more time thax sir

[Matthew_Ronan]

Thanks @Steve. I was actually just looking at that example on another thread you were working on. And already trying to work that example in.

Now I need to figure out the action/workflow to get the data to insert into that table when there is an entry made into the Exercise_Entry table (with the validation).

[Steve]

I’d think you’d want an action of type Data: add a new row to another table using values from this row with the expression I provided above as the action’s Only if this condition is true expression. Then attach that action as the Form saved event action for the Exercise_Entry table’s form view.

[Matthew_Ronan]

Thanks! I started to fiddling through setting up the actions/workflow. I’ll report back in a bit if I have any hiccups.

Thanks again for the help!

[Matthew_Ronan]

@Steve Thanks for the help with the validation, it works great. I got everything logging to that table except for one piece. I thought I had it figured out, but for some reason it just won’t work.

In addition to the User ID, Challenge ID, Exercise ID, and Date (that one was new) I need to insert a value from a different table. The value is coming from the Challenge_Exercise table where the Challenge ID and Exercise ID exist. I need to pull the Target_Reps value and insert it along with the previous 4 values during the action trigger. Here’s what I thought would do it…(added to the action trigger from above) Trying to set the ‘Target’ field on the Challenge_Progress table =

Any(
   select(challenge_exercises[Target Reps],
      AND(
                ([Challenge ID]=[Challenge ID]),
                ([Exercise ID]=Exercise ID]),
                (today()>=[Start Date]),
                (today()<=[End Date])
                )               
                )
         )

Any suggestions

[Steve]

At the very least, your expression is missing the [_THISROW] dereferences mine had. Those are critical.

See also:

help.appsheet.com

SELECT()

Gather column values from row in table or slice

[Matthew_Ronan]

Thanks for the tips and pointing me in the general direction for learning. I was able to troubleshoot and get what I needed to work. Still learning all the ins and outs of AppSheet.

Question, do you know if there is a way to update a field on a given table that is triggered by changes to a different table? Based on my trials and errors, the action to set the value of a column can’t be triggered by events on a different table (you can only add rows - and I don’t want to delete and re-add rows).

Objective: I am trying to update the Completed column for the row we inserted above in the Challenge_Progress table with the sum of reps for a given exercise

The validations and math to get the values and update the appropriate records is done. I just cannot figure out to trigger the action… I know I’m missing something.

[Matthew_Ronan]

Update… it appears that I got it working.

Action 1:
For a record of this table: Select the main table
Do this: Select ‘Data: Set values of some columns in this row’
Select the columns: Define the columns you want to update and the value to update them with

Action 2:
For a record of this table: Select the secondary table
Do this: Select ‘Data: execute an action on a set of rows’
Referenced table selection: Select the main table
Ref’d Rows: Define the value from a column of the secondary table that picks correct row in main table.
Ref’d action: Select the name of you gave for ‘Action 1’

Workflow
When this happens:

• Target Data: Select the secondary table
• Update Event: Select the action that triggers the workflow

Do this:
Reaction: Select ‘Change Data’
Workflow Action Name: Enter a name
Data Change Action Name: Select ‘Action 2’

Seems to be working perfectly

[Steve]

OUTSTANDING!!! Very well done!

[dohuuduy1985]

Please share the demo