Get last date entered filter

[TE21]

I have the following columns in one row

[max_load_1],[max_load_2],[max_load_3],[max_load_4],[max_load_5],[max_load_6],[max_load_7],[max_load_8],[max_load_9],[max_load_10],[max_load_11],[max_load_12]

[date_tested_1],[date_tested_2],[date_tested_3],[date_tested_4],[date_tested_5],[date_tested_6],[date_tested_7],[date_tested_8],[date_tested_9],[date_tested_10],[date_tested_11],[date_tested_12]

Once the Max Load data is entered the Date Tested date is auto populated and then the data is added into a table on a report.

The Max Load data is entered one at a time on different days. Sometimes not all 12 are used but the report is continuous for each row. When the report is generated each day I need to be extract/filter the Date Tested column that correlates to the last Max Load data that was entered.

If today the [max_load1] data was entered that generated the [date_tested1] then I would need that date to display in a pdf report. However, when there are 4 more generated on a table and now I am on  [Max_Load5] I need to be able to extract/filter the [Date_Test5] since that was the most recent.

Any help is much appreciated

[dbaum]

You can likely adapt one of the techniques described in a recent conversation about a similar need: Solved: Find next date in a list and give the column heade... - Google Cloud Community .

[TE21]

Thank you for the advise. However what I need is last added "Date Tested" value to show up in the "Report Date" column.

Meaning when I add a value into the "Maximum Load" column the date that get automatically populated into the "Date Tested" column needs to be added automatically into the "Report Date" column.

Any help is much appreciated.

[jkevz]

Just try this expression in the field [Date Tested] = UTCNOW()

[dbaum]

Maybe what you're looking for is:

MAX(LIST([date_tested_1],[date_tested_2],[date_tested_3],[date_tested_4],[date_tested_5],[date_tested_6],[date_tested_7],[date_tested_8],[date_tested_9],[date_tested_10],[date_tested_11],[date_tested_12]))

[TE21]

Again, I really appreciate your help here. I hate to be a bother and not sure if you can help but I actually need the value in the "Date Tested" column that matches the "Maximum Load" column to be displayed in the "Report Date" column.

the code you provided took the last date and added it. What I would need is the "Report Date" column to update for each Maximum Load entry. 

What is happening is we are breaking concrete at certain intervals as noted in the "Test Age (days):" column and when we break the concrete we send out the report. I need to make sure the "Report Date" shows the "Date Tested" date that matches the same column as the Maximum Load. I have attached the image for reference. 

It varies for each report but for this particular report on the 7 day break I would need the "Report Date" to display 3/11/2023

For the 28 day break I would need the "Report Date" to display 4/1/2023

For the 56 day break I would need the "Report Date" to display 4/29/2023

If I need to hire you for your help please let me know how I can pay you as this is very important to get right. I greatly appreciate your continued responses.

[dbaum]

---

@TE21 wrote:

the code you provided took the last date

---

Based on "Once the Max Load data is entered the Date Tested date is auto populated", it sounded like each date_tested value is populated only when its  corresponding max_load value is populated, in which case the 'last added "Date Tested" value' would indeed be the result of the expression I provided--i.e., the maximum date_tested value. Your most recent screenshot shows some date_tested values populated without a corresponding max_load value. If that's a real scenario, then indeed you need a different approach.

---

@TE21 wrote:

and added it

---

Added what where? The expression I drafted was for the App formula property of 'the "Report Date" column' (is that where you entered it?), based on my understanding that you wanted a row's "Report Date" column to recalculate every time the row is otherwise updated (is that what you wanted?). 

---

@TE21 wrote:

I need to make sure the "Report Date" shows the "Date Tested" date that matches the same column as the Maximum Load

---

If your rows do include date_tested values without corresponding max_load values, then you likely need something like either of the techniques illustrated in the thread I first linked you to.

Brute force serial comparisons

IFS(
ISNOTBLANK([max_load_12], [date_tested_12], 
ISNOTBLANK([max_load_11], [date_tested_11], 
...
)

Index of

INDEX(
LIST([date_tested_1],[date_tested_2],[date_tested_3],[date_tested_4],[date_tested_5],[date_tested_6],[date_tested_7],[date_tested_8],[date_tested_9],[date_tested_10],[date_tested_11],[date_tested_12]), 
COUNT(
LIST([max_load_1],[max_load_2],[max_load_3],[max_load_4],[max_load_5],[max_load_6],[max_load_7],[max_load_8],[max_load_9],[max_load_10],[max_load_11],[max_load_12])
)
)

---

@TE21 wrote:

important to get right

---

FYI: A different data design from the start likely would have facilitated the data manipulation you apparently need. For example, perhaps you would have benefited from segregated the date_tested, max_load, and maybe other values into a separate child table with a reference to your current table as a parent (i.e., use The "item-detail" data pattern - AppSheet Help).

[TE21]

I really appreciate your help. I am sorry this is such a complicated task but I am curious how I could get the "Date Tested" value to display in the "Report Date" column based on the "Max Load" entry?

If I enter in a value for [Max_Load1] then how do I tell the virtual column "Report Date" to copy the [_thisrow].[Date_tested_1] data into the "Report Date" column? 

I think the problem will be further complicated when I am entering data into [Max_load9] and I need the Date_tested_1 thru Date_tested_8 dates skipped and  only copy the [_thisrow].[Date_tested_9] date into the "Report Date" column.

Any help accomplishing this is much appreciated.

[dbaum]

Share a screenshot of the configuration of the "Report Date" column.

[TE21]

Thank you for your continued help. 

[dbaum]

That column configuration matches what I suggested based on what I've understood from your questions (although it seems unlikely that you need "Report Date" to be a constantly recalculated virtual column rather than a regular data source column that's recalculated for a row only when that row is updated).

You noted originally that all the max_load and date_tested columns are "in one row". Are all those columns indeed in the same Concrete_Sample table as the Report_Date column?

If yes, then what result do you get for "Report Date" when you have data like the following?