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

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

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])
)
1 Like

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).

1 Like

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.

1 Like

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!

1 Like

@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

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

See also:

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.

1 Like

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

3 Likes

OUTSTANDING!!! Very well done!

2 Likes