Hello! I'm curious about the feasibility of a...

AppSheet AppSheet Q&A
1

Hello! I’m curious about the feasibility of a validation pattern.

I have a table called ‘Records’ which aggregates information contained within a few referenced tables.

Information must be aggregated based upon their Project ID, Creation Dates, Kilometers, and Lane & Lift entries. There can be many ‘Date’ records for a project, many ‘Kilometer’ entries for a date, and many ‘Lane & Lift’ entries for a kilometer. See the flowchart here: https://i.imgur.com/xRI0GwM.jpg

I would like for duplicate ‘Records’ to not be allowed; i.e. records having the same project, date, kilometer, and lane & lift.

The current form has 4 dropdown menus and it filters them appropriately; i.e. when you select a specific date it will only show ‘kilometer’ entries that occured on that date. However, it does not remove the options to prevent duplicate entries. It would be convenient if the lists filtered out options which have already been created, to simplify the user experience… I know how to do this on individual columns; is this more complex pattern possible in AppSheet?

For now I am preventing duplicate entries by having the ‘records’ table use a generated primary key based upon the project-date-kilometer-lane&lift pattern. But this solution doesn’t help the users to know which records have yet to be made.

2

I’m having trouble imagining what user experience you envision. Could you walk me through it, step by step?

3

There are two tables for this problem: “Tickets” and “Reports”. The “Tickets” table contains the project, date, kilometer, lane & lift data which is collected (among other data).

The manner in which the data is collected does not facilitate using the “Reports” table as a parent record for “Ticket” data, at least at the point of data collection. However, at some point the collected data must be aggregated in a different form (the one shown in the flowchart) than the form in which it is collected. A report must exist for every branch of the tree in the above flowchart example. this ‘tree’ is dynamic in that records are added daily and each branch is unique.

To achieve the goal, I have created a virtual column in the “Tickets” table which combines the project-date-kilometer-lane&lift columns into a group identifier, and the primary key in the “Reports” table is the same.

The ideal would be that these reports are generated automatically, for each unique branch of the tree (I can do this in the back end but I would like to avoid this if possible). One solution is to just have the users manually add a report for each unique tree manually. To make this process easier, it would be good to have the dropdowns filter out branches which have already been created. In theory, if every possible branch exists, none of the dropdowns should populate.

Does this make sense?

4

Assumptions:

  • You’re trying to populate columns in the Reports table.

  • a virtual column in the “Tickets” table which combines the project-date-kilometer-lane&lift columns into a group identifier and the primary key in the “Reports” table is the same are both named Group ID.

For the Valid_If formula for the Project column of the Reports table:

=SORT(

SELECT(

Ticket[Project],

OR(

([Group ID] = [_THISROW].[Group ID]),

NOT(IN([Group ID], Reports[Group ID]))

),

TRUE

) )

  1. SORT(…) sorts the generated list of Project column values. This step is entirely optional.

  2. SELECT(Ticket[Project], …, TRUE) gathers distinct (per TRUE) values from the Project column of the Tickets table from rows that match the given criteria (below).

  3. OR(…, …) requires that at least one of the given criteria (below) is TRUE.

  4. ([Group ID] = [_THISROW].[Group ID]) matches the report for the Tickets row. This will only match once the Group ID for the report is assembled completely, and is needed to ensure the validation rule validates the completed rule. If this exception isn’t present, the step below would cause the validation to fail.

  5. NOT(IN([Group ID], Reports[Group ID])) matches only those rows in the Tickets table in which the Group ID column value does not occur in the Group ID column of the Reports table. This excludes Tickets rows with configured reports from consideration.

The above effectively gets a list of projects from tickets that do not currently have configured reports.

For the Date column of the Reports table:

=IFS(

ISNOTBLANK([Project]),

SORT(

SELECT(

Ticket[Date],

OR(

([Group ID] = [_THISROW].[Group ID]),

AND(

([Project] = [_THISROW].[Project]),

NOT(IN([Group ID], Reports[Group ID]))

)

),

TRUE

)

) )

  1. IFS(ISNOTBLANK([Project]), …) will only produce a result (a list of Date values from the Tickets table) if the current Reports row’s Project column is not blank. This effectively requires the user select a project before being given a list of dates.

  2. SORT(…) sorts the generated list of Project column values (below). This step is entirely optional.

  3. SELECT(Ticket[Date], …, TRUE) gathers distinct (per TRUE) values from the Date column of the Tickets table from rows that match the given criteria (below).

  4. OR(…, …) requires that at least one of the given criteria (below) is TRUE.

  5. ([Group ID] = [_THISROW].[Group ID]) matches the report for the Tickets row. This will only match once the Group ID for the report is assembled completely, and is needed to ensure the validation rule validates the completed rule. If this exception isn’t present, the validation would fail.

  6. AND(…, …) requires that all of the given criteria (below) must be TRUE.

7 ([Project] = [_THISROW].[Project]) matches only rows in which the Project column value matches this report’s Project column value.

  1. NOT(IN([Group ID], Reports[Group ID])) matches only those rows in the Tickets table in which the Group ID column value does not occur in the Group ID column of the Reports table.

The above effectively gets a list of dates from tickets for this report’s project that do not currently have configured reports.

Similarly, for the Kilometers column of the Reports table:

=IFS(

ISNOTBLANK([Date]),

SORT(

SELECT(

Ticket[Kilometer],

OR(

([Group ID] = [_THISROW].[Group ID]),

AND(

([Date] = [_THISROW].[Date]),

([Project] = [_THISROW].[Project]),

NOT(IN([Group ID], Reports[Group ID])

)

),

TRUE

)

) )

And the Lane & Lift column:

=IFS(

ISNOTBLANK([Kilometer]),

SORT(

SELECT(

Ticket[Lane & Lift],

OR(

([Group ID] = [_THISROW].[Group ID]),

AND(

([Kilometer] = [_THISROW].[Kilometer]),

([Date] = [_THISROW].[Date]),

([Project] = [_THISROW].[Project]),

NOT(IN([Group ID], Reports[Group ID])

)

),

TRUE

)

) )

5

+Steve Coile Thanks for the comprehensive response Steve - this solutions works for the above problem

Currently I am experimenting with another solution which, in a roundabout way, creates the records automatically!