GET VALUE for Table 1 from Table 2 WHERE Table 1 R...

Rosemary · 07-23-2021 11:37 PM

Hi
I am working on a little app to record and evaluate diet information. At the end of each day the user enters their consumption info (which is captured with a Diet Diary App) . This is a variables like calcium, protien, vegetables and importantly, CALORIES for each day.

I have a main table called DATA which contains a column called TARGET CALORIES. This is used for a bunch of calculations that compare the person’s ACTUAL CALORIES CONSUMED to their TARGET CALORIES.

I want the user to be able to change the TARGET CALORIES every now and then and have that change apply to all subsequent days’ records until they change it again.
I don’t want them to have to enter TARGET CALORIES every day
And I don’t want to have to do this manually in the Excel Spreadsheet (which is what I have been doing).

I have made a table called CAL_TARGETS to store the calorie targets as entered by the user.
This table has a One to Many relationship to the DATA Table.
There is only ever one CALORIE TARGET for any given day (row) in the DATA table but any given record in the CAL_TARGETS table will apply to many records in the DATA Table.
I need the previous target to remain on previous days and the new target to be applied to each subsequent day as the data is entered.

CAL_TARGETS TABLE
has 4 columns ROWNUMBER, STARTDATE, ENDDATE, CALORIES. Except for RowNumber (obviously) this table is updated via a UX called “EDIT CALORIE TARGET”.

DATA TABLE
has a lot of columns including DATE (one date per row) and CALORIE TARGET.
DATA Table has consecutive rows for every day since 23 March 2021.
I want DATA [CALORIE TARGET] to contain the value from CAL_TARGETS [CALORIES] where DATA [DATE] falls within the [STARTDATE] and [ENDDATE] of a record on CAL_TARGETS.

CAL_TARGETS TABLE
START DATE END DATE CALORIES
23/3/2021…31/05/2021…1673
01/06/2021…30/06/2021…1790
01/07/2021…31/0702021…1800

DATA TABLE
DATE…CALORIE TARGET
15/04/2021…1673 ( and the same for all subsequent rows until next record on CAL_TARGETS)
23/06/2021…1790 ( and the same for all subsequent rows until next record on CAL_TARGETS)
10/07/2021…1800

I have read a number of Support Pages, Question Threads and watched a few AppSheet YouTubes but I haven’t come across anything that addresses this particular challenge. I have experimented with combinations of AND() and SELECT() and linking the two tables.

Thanks
Rosemary

Rosemary

HI There
I see no one has replied to my question yet. Perhaps I offered too much detail…

To simplify:
I want to know how to write an expression in a column in my main table that picks up a value in a different table based on the date of the record in the main table.

I think it has something to do with referencing between tables but I don’t understand the AppSheet Syntax well enough to write an expression that does this.

thanks
Rosemary

JackRG

Hi Rosemary, is it possible that you need to use the Valid_if field to create a type of depending dropdown selection. Check this video:

https://youtu.be/HreJAInAjnU

Steve

Try this:

ANY(
  SELECT(
    CAL_TARGETS[CALORIES],
    AND(
      ([STARTDATE] <= [_THISROW].[DATE]),
      ([ENDDATE] >= [_THISROW].[DATE])
    )
  )
)

GET VALUE for Table 1 from Table 2 WHERE Table 1 Record's Date FALLS BETWEEN Table 2 Record's START& END DATE