Solved: Referencing in unrelated tables..

RoryF · 03-13-2022 01:33 PM

Hi Everyone...

I am a bit stumped with something that I thought would be reasonably straightforward.

I have 2 tables - one called "MasterData" and one called "AppSettings". MasterData is what is sounds like - it has all of the app data that I am using; multiple rows with a unique id as the key (called "Submission ID"). The other table has 1 row - and is basically used just to hold some "global" variables I want to set. This includes an email address that I want to send a report to. Screen shots of these 2 tables are shown below.

The problem I have is in "relating" these 2 tables to each other - as there are no common fields. There is no Parent/Child relationship as found in classic Order/Line/Item examples that I have come across.

What I want to do is generate a report - the body of the report will contain data from MasterData. The email address will be stored in the AppSettings table. I have managed to get a virtual column added to Appsettings with a reference to MasterData which, when tested, returns all of the Submission ID's from MasterData (so that is a bit of progress). However I can't seem to be able to apply a filter here (maybe I need a syntax example?). I would like to, for example, only show Submission ID's dated within the last week (7 days). I assume this would go in the "Expression Assistant" of the "RefMasterData" virtual column but I can't seem to get the syntax right (I keep getting an error saying that the expression cannot be used on a List..). Maybe there is somewhere else to apply a filter..?

Any thoughts?

Thanks,

RoryF

WillowMobileSys

There are two things here - 1) expression possibilities to get your report data 2) how to generate and email the report

1) Expression possibilities to get your report data

You do NOT need to have relationships between two tables to be able to retrieve values from one to compare against in the other.

Because your "AppSettings" table has only a single row. You can take advantage of that to help simplify that part of the expressions.

Looking at your data, I will assume that Username and Name are the same values. So an example expression to get the last week of reports submitted by the chosen Username might be:

SELECT(MasterData[Submission ID], 
       AND([Submission Date] >= TODAY() - 7, 
           [Name] = ANY(AppSettings[Username]))
)

This expression returns a list of Submission ID's. You can adjust the AND() portion to utilize other filter criteria from your AppSettings if you wish to further refine the filtering.

2) How to generate and email the report

Once you have the ability to retrieve the list of filtered Submission ID's, then it's a matter of how to use it to generate the report. You could just list the Submission ID's but that likely isn't very useful to the recipient. Instead, it might be better to show all of the pertinent related Master Data and then automatically attach that report into an email to send.

For this you will want to use a template. A template allows you format and arrange the data on the report. The most common template is Google Doc. In this template, you would use the expression created above to get the list of ID's - pointers to the rows - and then your template can cycle through each row to get any of the related data to place on the report. When complete the generated report can then be attached to an email and sent to some dynamically determined email list

Here is an article below to get you started. If you search on "templates" and/or "email" you will find several other articles that might be of help.

https://help.appsheet.com/en/articles/961741-email-templates

I hope this helps get you started. There are a lot more details, I know, just come back to the Community ask your future questions.

View solution in original post

WillowMobileSys