Filter rows and perform an ACTION

[ConnieMaldonado]

I need to export a file periodically with a filtered condition. Each time I export, I need to only export records not exported previously.

I set up an action to execute a sequence of actions: 1. Export File and 2. Mark Records as Exported. I would like to export a file for all records from a table in which [Status]=“Green” AND NOT([Exported]=“Yes”). Once the file is exported, I would then like to set [Exported]=“Yes” for all records with that same condition.

For the “Mark Records as Exported” action, I am having issues completing the “Referenced Rows” logic. I included the following:

AND([Status]=“Green”, NOT([Exported]=“Yes”))

but receive the error: The expression is valid, but the result type “Yes/No” is not one of the expected types: List.

I tried using a FILTER statement to identify the records using the following filter statement, but I can’t find a Referenced Action that would allow me to set [Exported]=“Yes” for the filtered records. Is there a better way to do this?

Filter(Tasks, AND([Status]=“Green”, NOT([Exported]=“Yes”)))

[WillowMobileSys]

The Referenced Action is one you create. You’ll need to create the Action for the Tasks table that sets [Exported] = “Yes”. Once you do, the dropdown for Referenced Action will show it as an option to select.

As far as a better way, if you have the export part figured out already then I think you’re on the right track.

[ConnieMaldonado]

Ok, John. Fair enough, and thank you. When I create the action, however, which action is it? The only action that appears plausible is “Data: set the values of some columns in this row”, but I want to set the columns for the filtered condition - i.e., all records that satisfy a condition, I want to set [Exported]=“Yes”

[WillowMobileSys]

Yes, that is the correct Action type to create. Your previous Action with the FILTER() expression will send EACH of the matching rows to the Referenced Action.

So if the FILTER() returned 23 rows, there would be 23 calls to the Referenced Action to set the [Exported] value in each of those row to "Yes’.

[ConnieMaldonado]

That worked. Hallelujah.
Now another question: Is there any way to run a report from an action rather than schedule the report? I’m trying to run a report to create a CSV file for records with a certain criteria, and then set exported = “yes” for the records in the data.

I’ve completed the report, and I’ve completed the “set records” part with an action. I now need to link the 2 together so it runs automatically.

[WillowMobileSys]

Right. The issue is that you cannot invoke an Action directly from a Report.

AppSheet does have an API. I have not don this but it is my understanding that you could invoke the AppSheet API, specifically an action, using a Report Webhook step.

Below is an article about doing just that:

help.appsheet.com

Invoking an Action

How to invoke an action using the API.

You might also find this useful:

https://www.appsheet.com/Support?q=AppSheet%20API&hPP=10&idx=help&p=0&is_v=1

[ConnieMaldonado]

Thank you, I really appreciate it. I’m sure I can figure something out!!

[ConnieMaldonado]

I re-read your response. You said there’s no way to invoke an action from a report, but is it possible to invoke a report (i.e., run a report) from an action? I don’t think so, as it’s not one of the choices in the drop-down (when creating actions). I’ll look at workflows to see if that’s helpful, and if not, I’ll look at APIs or create a business rule to manually run the action after the report is run.

[WillowMobileSys · Accepted solution]

Actually, Yes you can run the report from an Action!! But you had said that you wanted to run the report automatically which I assumed meant at some timed period separated from user activity.

All of the steps you have available in a report, you can implement in a Workflow and then use an action to fire off that Workflow. In this case you can crate and send your report file AND then set the Exported value.

But this is not automatic, user activity within the app is needed to create the proper changes that triggers the workflow. If no users are using the app, the Workflow doesn’t fire and no report is generated.

AS a side note: there are really only 2 differences between Reports and Workflows:

1. Reports can automatically fire based on some pre-determined time. Workflows cannot.
2. Reports cannot directly activate Actions - i.e. make data changes. Workflows can.

[ConnieMaldonado]

Thank you! You’re correct, I do want things automated, but a workflow might be ok. Thanks again!

[WillowMobileSys]

One last thought. If the report absolutely needs to be sent consistently regardless of user activity, then a Report rule is the way to go.

But if you expect a lot of user activity and just need the report sent occasionally and not at some preset time, then you can simulate an automated report based on the user activity.

For example, when rows are added, maybe you keep a count. Once you have reached x number of rows, a Workflow fires to export, send those rows in the report and resets the counter.

Or maybe its based on days, track the number of days since the last export. As rows are added check if the number of days has been surpassed, if so kick off the report and the set the sent date to reset. In this case as opposed to a Report, you are not sending the file precisely x number of days. Only once you have surpassed x number of days. Maybe a holiday or weekend was in the middle, the Workflow simply waits until user activity again and checks if its x days or greater to fire.

Just some thoughts as workarounds!

[ConnieMaldonado]

Oh wow. Thank you. I would have never thought of that!