Creating a Report Generator Loosely Based on an Automation

[mykrobinson]

Please forgive me for using this place as a virtual notepad at times, but it can be beneficial for me to talk things out in public, plus the conversation may help someone else in the future.

I have an automation that runs weekly and generates a report based on attachment template that looks a little something like this:

What I would like to do is create a "report generator" that allows a user to select a date range and export a report to be emailed based on their selections. I would like the option to select a date range, as mentioned above, select specific or all patients, specific or all hospitals, and specific or all providers.

I'm not sure how to approach this but i will be digging through AppSheet tutorials and the manual this morning to figure this out. If there is a certain guide you feel may be helpful in pointing me in the right direction to get started on this, I welcome any tips.

As always, not looking to be spoonfed an answer, as opposed to helping narrow my focus in a direction that is more helpful than it is an endless rabbit trail of links that end up confusing me 😂

Thanks for your time.

[mykrobinson]

Starting my journey with analyzing this app:

https://www.appsheet.com/templates/Reports-that-users-can-customize?appGuidString=69a02679-bfe6-45fa...

[SkrOYC]

Yep, that template seem like the way to go.

I always configure my bots using an Add event, I don't have the need for reports on deletion and updates seem like hit or miss.

So I have several tables just for reports, and since the automation is triggered on Adds, I can be 100% sure it won't be triggered by mistake and that they will be triggered.

These tables are different depending on the need but the basics IMO are:

Column Name	Column Type	Formula	Purpose	Description
Id_column	Text	UNIQUE("UUID")	Key, obviously	You know what this is
Filter1	Depends on your need, DateTime for example	Null, or consider adding a valid_if that prevents this to be older than the oldest record	Let's consider this a Start_DateTime	A field that let's you select the Start DateTime for a set of rows
Filter2	Depends on your need, DateTime in this example	Null, or consider adding a valid_if that prevents this to be later than the newest record	Let's consider this a End_DateTime	A field that let's you select the End DateTime for a set of rows
Filter3	Enum/EnumList?	Enum: Make sure that the Valid_If is a Select(Table[Category]...) so that the options available are taken from the result of the previous selected range. EnumList: You can use the same Valid_If but you could also use an InitialValue that selects all of the above options by default, for example.	This will help you select a more granular category from the rows inside the range selected before.	BaseType shoud be the same as the [Category] column mentioned before. You can repeat this kind of column how much you need. Eg: Gender on a list of people or people's records
Filter4.1	EnumList BaseType Ref	A Valid_If with the previous filters applied. Something like: Filter(Table, AND([Date]>=[_THISROW].[Filter1].....))	Option 1: This will let the user control the final set of rows added to the report.	This is helpful if the Label for that column is descriptive enough for the user to decide if a record is considered or ignored. Cons: Generally, labels are not descriptive enough
Filter4.2	List BaseType Ref	This is easy, AppFormula with the same Filter() as before	Option 2: This won't let the user manually select the records but you can show more info.	Better UI in general, because an Inline can be a lot of things. Cons: Kinda. If you give them enough filters before this one they won't need to manually select records to be ignored, so make sure you have enough filters!

Tip: Both Filter 4, EnumList BaseType Ref as well as List BaseType Ref let's you do List Dereferences, now it's even mentioned on the docs. So if you think about it, you could even use this to filter a parent but show children records of those parents on a report instead of the parents, for some reason. Anyway, I love the flexibility of the platform eventhough that seems to be a problem for some

[SkrOYC]

Btw, extra little tip.
On the form view for this table, you would expect a "Send" button or similar instead of "Save".
You can go to UX->Localize->Save and add an expression like this:

SWITCH(
  CONTEXT(VIEW),
  "YourFormView", "Send report",
  "Save"
)

SWITCH() - AppSheet Help 

[mykrobinson]

@SkrOYC you able to assist with some date math?

I'm crafting an expression to extract the data the user selects, and I need to pull records where the "Seen by Provider"  [Seen Timestamp] values fall between the user selected start date and end date. I realize this should be simple greater than, less than type expressions, but i tend to get these wrong frequently for some reason 😂

I "think" i need something like:

SELECT(Seen by Provider[_seenID],
AND(
DATE([Seen Timestamp])>=[_THISROW].[Starting Date],
DATE([Seen Timestamp])<=[_THISROW].[Ending Date]
)
)

 I realize I can just test this.... but are my greater than/less thans in the right order, or do i have them backwards?

*EDITED TO FIX SYNTAX TYPOS

[mykrobinson]

UPDATE: It looks like my SELECT expression worked on the first try, we're getting somewhere! One question, though.... How do I link this form to a button in the menu? I don't see any option to make this form view appear as a Menu entry 🤔

I may have figured it out myself shortly, just documenting the journey.

[mykrobinson]

Yeah, this is a weird one, I am not getting the option to edit the View Type or Position 🤔
Bug or feature?

[SkrOYC · Accepted solution]

Hi @mykrobinson, I was out for a moment.

Your expression looks fine, so it works as you figured it out.

About the form view, ignore the system created one and make a copy or a new one. System views comes with some fields hidden.

The new one will give you the option to put it on the Menu or anywhere else.

PS: Any user-created form, detail or inline (ref) view will override the system created one on the default actions. So if you create a form view, AppSheet will ignore the system created one

[mykrobinson]

Thank you!

[mykrobinson]

@SkrOYC if you are still around, i could use a hand with setting up the spreadsheet template 😞

The search filter is working well and displays a bit of a list onscreen. I have not got the email part working yet. I understand how to get it to send the email but i am having trouble with my spreadsheet.

So, i have a separate table called ProviderReportRequest , that that table has a virtual column called [Matching Items] that creates a list of primary keys from the Seen By Provider table that match the selected dates.

I need to configure my template to access data referenced by the primary keys in the Matching Items list. So if I am searching for "Seen by Provider" records between 2022-07-01 and 2022-07-14, i need to return all rows matching that timeframe between those dates, but of course, coming from a "foreign" table, since this form is working off a table that just logs the report requests.

Not sure if I am wording my question correctly, but for example, the first column in the table template is _seenID, and i want that to list the _seenID values (primary keys in the Seen by Provider table) that show up in the [Matching Items] virtual table. All subsequent columns downstream would also be based on the information that is pulled in the Matching Items list.

So, the expression i have in the first column is:

<<Start:<<[Matching Items]>>.<<[_patientIdentifier]>>.<<[_seenID]>>

But something tells me this is going to generate an error 🤔
How do i reach into the Seen by Provider table based on the results in the Matching Items list?

[SkrOYC]

Matching Items is just a list of Row keys from "Seen by Provider" table. When you call this list inside your Start: expression, anything after the Start: and before the End is evaluated in the context of each row.

You have the ProviderReportRequest table just for reports, you don't actually need to link to any table from it. You could report data from a lot of tables from this single one.

You already have a list of rows inside of your Matching Items, so you don't need to do anything else rather than just call them inside your Start:

Now, this is all assuming you have a bot configured with the ProviderReportRequest table and triggered on Adds, if for some reason you started to configure this on your "Seen by Provider" table you have to change it.

Please give more info about your current setup after the addition of the ProviderReportRequest table

[mykrobinson]

Im not great with words for this one but i will try.
I believe it is configured as you said. The ProviderReportRequest table is simply there to allow the end user to fill in a form to select the dates to set boundaries for information they want to see from the Seen by Provider table. Matching Items is a virtual column and as such is not stored in the ProvierReportRequest table.

I wanted to build an automation/bot that looks for additions to the ProviderReportRequest table, extracts the requested information, and formats it into a spreadsheet, then emails to the user who requested it.

I guess what i need to understand is how to call the desired values in my Start in the template. I've done templates but it was always pulling data from the table that triggered the automation, not from another table

[mykrobinson]

Thinking i need to add a Filter statement to the Start based on the results of Matching Items 🤔

[mykrobinson]

wait, i think i got it...

So, if my first column is _seenID, my start should be

<<Start:<<[Matching Items]>><<[_seenID]>>

I think this will work, then i can just reference the desired column name for the other columns i want shown because they will be referenced already from the Start statement. 

Going to try this, it makes sense in my head.

[mykrobinson]

UPDATE: It generated an error. Posting it here, but going back to re-read this and see if I can figure out what i did wrong

Name
Handle new ProviderReportRequest record
Created TimeStamp
7/20/2022 7:58:16 PM
Status
Error
Current Step
Handle new ProviderReportRequest record-returnStep
Error Message
Error encountered in step with name [Send an email]: Error: 'Handle new ProviderReportRequest record' task 'Send an email' Attachment template. Expression '<<[Matching Items]' is invalid due to: Expression '<<[Matching Items]' was unable to be parsed: Expression '<<[Matching Items]' does not match the expected format of an AppSheet expression.. Error: 'Handle new ProviderReportRequest record' task 'Send an email' Attachment template. Start expression '<<[Matching Items]' should generate a List of Ref values. Please verify that it generates a List and that the contents of the List are Ref values. Ref values should come from the 'Key' column of the referenced table.

[mykrobinson]

I think i see what i did wrong, i need to remove the arrows in front of [Matching Items]

Trying again

[mykrobinson]

SUCCESS!!!!

Thank you, @SkrOYC for helping me think through this!

[SkrOYC]

You got it! 😉