Solved: Insert Data into Table B from Table A ONLY if a un... - Page 2

Matthew_Ronan · 03-02-2020 05:46 PM

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

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

View solution in original post

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