Form Navigation and Workflows

[Markus_Malessa]

I’m having some continued problems getting with related entries into a table and getting a workflow to execute the way I would like, so I’m hoping the community of experts could possibly give some pointers.

The main table is TWUAOrders with the most important fields:
[ReportingRequirement] - EnumList (“In-State”,“Out-of-State”,“Other”)
[Related WaterUseDatas] - Ref to WaterUseData Table
[Most Recent WaterUseData] - Selects only the last entries for [Related WaterUseData]

Related table WaterUseData:
[Date] - This gets generated based on a dynamic expression from the TWUAOrders info
[Amount_Pumped] - Ideally needs to be required
[TWUAUseCategory] - Enum (“In-State”,“Out-of-State”,“Other”)

Workflow:
Option 1 -
Needs to get triggered on WaterUseData ‘ADD’ but only after the TWUAOrders Ref has a current entry for each of the “In-State”,“Out-of-State”,“Other”. This is accomplished by comparing the current WaterUseData [TWUAUseCategory] against the last selected choice in the TWUAOrders entry and will only get executed then. I have an action button in the TWUAOrders_Detail view for each of the 3 options so this current setup is highly dependent on the user clicking these action buttons in the proper sequence. Is there a possibility under this scenario to save a WaterUseData row and then proceed to the form of the next WaterUseData form?

Option 2 -
Have a single action button under TWUAOrders_Detail that automatically creates each of up to 3 entries in the WaterUseData where it sets the [Amount_Pumped] to ‘null’, then navigates to the TWUAOrders [Most Recent WaterUseData] inline table in quick edit mode at which time the [Amount_Pumped] would be required so that the user has to overwrite the null values. Workflow would then trigger only if the [Most Recent WaterUseData] [Amount_Pumped] values are not null. Again this scenario also heavily relies on the user to actually change the null values as apparently the quick edit on a table does not enforce the required [Amount_Pumped] field if that field uses an expression for the required_if check.

If anyone has a different approach I would also very much appreciate any input.

[Steve]

What are you trying to accomplish?

[Markus_Malessa]

I guess it’s a bit complex, but as an example lets say I have a TWUAOrders row Identified by 21-001 with [ReportingRequirement] of “In-State” and “Out-of-State”. Based on that lets say I would like the user to enter 2 new entries into the WaterUseData table related to TWUAOrders by [TWUAOrders_fk] for the month of October. So something like this in WaterUseData:

Date	TWUAOrder_fk	TWUA Use Category	Amount	Units
10/28/20	21-001	In-State	10000	Gallons
10/28/20	21-001	Out-of-State	45000	Gallons

I would first off like to guide the user to make sure they enter 2 values in the WaterUseData table for the month of October related to TWUAOrder 21-001 by either providing the same form twice in succession, or by entering the entries automatically and then forcing the user to change the Amount from null to an actual value.
Second I would like a workflow to trigger only once both entries are complete that checks a running total against a max allowed in the TWUAOrder reference.

I hope that clears it up some more. Please let me know if I need to explain anything further.

[Steve]

Walk me through how the user would interact with the app for this process. Would the use add a new TWUAOrders row, then be automatically prompted to enter the needed WaterUseData rows? Or would the WaterUseData rows have already been entered when the user goes to add the TWUAOrders row? Or something else?

[LeventK]

@Steve
This was the workflow template that I have suggested @Markus_Malessa earlier.

docs.google.com

Email_Template_Community

Water Usage Data In-State Period Water Used (Gallons) <<Start: ORDERBY(FILTER([TWUAOrders_fk].[Related WaterUseData],"In-State" = [TWUAUseCategory]),[TWUAUseCategory],FALSE,[Date])>><<[Period]>> <<[Water_Use_Gallons]>><<End>> Out-of-State ...

[Markus_Malessa]

@LeventK yes that email template is directly related to what I’m trying to figure out. The template and the workflow itself are working, it just needs to be optimized for how to run a user through the process of entering the WaterUseData entries and an improved IF condition for when to run the workflow.

[LeventK]

Good to hear that @Markus_Malessa. I believe @Steve will guide you well for the rest.

[Markus_Malessa]

The row for TWUAOrders would have been entered prior to WaterUseData entry, so entirely separate actions. The WaterUseData row(s) would get entered on a monthly or weekly basis for up to 2 years.

So lets say TWUAOrders row gets created on 10/3/2020 for In-State and Out-of-State. Then sometime between 11/1-11/15 an internal user would enter WaterUseData rows for In-State and Out-of-State for the month of October as expressed before.

My first idea is have up to three action buttons visible only if TWUAOrders [ReportingRequirement] has the relative category in it.

My workflow in this specific case is triggered only after the WaterUseData entry for Out-of-State is added. The potential problem, the user enters Out-of-State first and the workflow gets triggered prematurely since the workflow triggers if the [1st Year Total (Gallons)] is at 90% of [Order Max Amount (Gallons]/2. Or the workflow does not get triggered at all if they fail to add an amount for Out-of-State.

Second idea is the “Add Water Use Data” which just creates two entries automatically presetting all values except for the [Amount_Pumped] field in WaterUseData table, after creating both entries I have the app auto navigate to the WaterUseData inline view in quick edit mode so that the user enters the [Amount_Pumped] field. In this case the workflow would trigger after Out-of-State is ‘saved’ as in Updateds_only. Similar issue here, if they don’t change both values the workflow may not be triggered.

The logic for the second scenario takes the dates of the last entries for each In-State and Out-of-State to create the new rows by either incrementing by month or week of the last entry.

The last item to consider is that I don’t want the workflow to trigger after just one entry in this case because I thought of this as well. In that case the receiving email party would receive an email twice potentially in quick succession each of the email stating different values for the total.

[Steve]

Markus_Malessa:

Second I would like a workflow to trigger only once both entries are complete that checks a running total against a max allowed in the TWUAOrder reference.

This sounds like the actual goal: check a running total against a max allowed in the TWUAOrder reference. Everything else is just how to get there. Checking the running total is easy enough, but what happens if no user enters any value for a given TWUAOrder?

[Markus_Malessa]

@Steve The no user entering any value is also a potential problem. The only way I have thought about that scenario is a separate workflow that would check how many entries there should be any given month lets say arbitrarily the 15th of each month since data is supposed to be provided by the 10th, and then the workflow would check Count() of WaterUseData entries against months between start date of the TWUAOrders entry and the current month. This would alert us to potentially missing values.
Ultimately that process should result in a list of TWUAOrders that would need to be ‘manually’ checked for the presence of all entries.
But you are correct checking the running total against max alllowable in TWUAOrders reference is the ultimate goal.
Since late Thursday I have come up with a way to check if both of the last entries do not contain ‘null’ for the last entries, so that would work in conjunction with automatically entering the WaterUseData entries but would still be susceptible to the user not changing the null value.

[Markus_Malessa]

@Steve See the pic below that checks if the current WaterUseData entry [TWUAUseCategory] equals the TWUAOrders [ReportingRequirement] in the last position.
So if a TWUAOrder actually has Out-of-State and Other as [ReportingRequirement] then the workflow would trigger if the WaterUseData entry [TWUAUseCategory] was ‘Other’. Hence I would possibly need a guided flow to get these in unless there is a better way.