Re: Auto Number Reports Using Multiple Tables

TE21 · 10-28-2021 08:11 AM

I have an app that generates two kinds of reports each day. The two tables I am using are “Test Results” and the other is “Daily Report”

“Test Results” have multiple tests per day that make up one report. “Daily Report” is a single document generated daily.

I need to be able to sequentially number the reports. For instance, the “Test Results” report would be 0001 today and “Daily Report” would be 0002 today.

Tomorrow, “Test Results” would be 0003 and “Daily Report” would be 0004. So on and so forth…

I have a [unique] column and a [date:] column in “Daily Report” table, and I have a [key] column and [date] column in the “Test Results” table. I have been trying to figure this out using these but can’t get it right. Every day a new report will be generated HOWEVER not every day both of these reports will be generated. I will be adding more tables (one table = one report) so any advice on how to continue this numbering sequence using multiple tables would be hugely appreciated!!! Additional reports (tables) will be labeled “Field”, “Lab”, “Wet”, “Dry”

Could someone tell me if I am doing this right and help me please. Very much appreciated in advance!

WillowMobileSys

I assume your intent is to have a unique sequential number for each report across all the tables - Test Results, Daily Report, Field Report, Lab Report, Wet Report, Dry Report?

First, the cautionary tale…

…Because AppSheet is a distributed mobile system, it is near impossible WITH CONCURRENT PROCESSING to correctly maintain a sequential sequence. In other words, if your system has the possibility of multiple users performing reports simultaneously OR having the system trigger reports concurrently, you will likely encounter issues with the sequential numbering.

If it is not necessary to use sequential numbering then I would recommend not even attempting it. Just assign an ID with the UNIQUEID() function and move on.

On the other hand, if your reports will be automatically generated, say by a bot at the end of the day, then you can manage the sequential number by scheduling the report bots to run with ample time in between to prevent any conflicts with numbering - maybe set them to run 5 minutes apart.

Ok, now on to my recommendation to easily handle the sequential numbering…

…I would recommend introducing a Reports table that acts as a Parent table. This table would track all of the common details about the reports - Report ID (your sequential number), Date ran, Date(s) reported on, Report Type, Report Title, Description, etc. Your other tables would contain all of the other report specific details and would use the Report ID to tie the two tables together.

With this Parent/Child setup, you can easily assign the next Report ID with an expression like MAX(Reports[Report ID]) + 1. More importantly, you will have all of the common report details in a single place which means changes to that info in only one place rather than making the same change to 5+ segregated report tables.

I hope this helps!!

TE21

Thank you for the warning shot! I have read a lot about the sequential numbering and the potential horror that could come from it. I planned on having the report generation be the only manual aspect so that the sequence wouldn’t be an issue. I am planning to use the UniqueID as a work order type number just in case…

I will give this a shot! Seems like it should work. Didn’t think about the Parent/Child set up. Thank you very much!

Marc_Dillon