I have a data collection AppSheet application...

ux
(Eleshia Robinson) #1

I have a data collection AppSheet application used for an inventory project.

I have created a QA view so that a manager can do a quality check on a % of the inventoried assets.

During the QA process, the manager can make changes to any of the fields to correct any inaccuracies entered by the inventory technicians.

I have added a field called “Changes Made During QA” that is a Y/N field to record when there was a change made during QA.

Because the QA manager may go into the Form view (editable) without making any changes, and they can save the data with no changes, I cannot assume that visiting the Form view means that the “Changes Made During QA” field should be set to Yes.

Is there a way to set up an event that gets triggered ONLY if the data in the record is changed? FYI - Also need to check if related records in another table have changed.

(Bituminous Roadways) #2

There are column types called “ChangeCount” and “ChangeTimestamp”

Assuming the data collector does not edit, but just enter it, the change counter and/or change timestamp would be blank.

You could use that as an indicator.

the change counter increments every time a record is changed, which means if they use the quick edits, it will increment every time they update one column.

you could also use an expression:

if ( isblank([change count]),

“false”, :true")

Or you could use a change data workflow that triggers an action on changes (or even changes by a certain user if you dont want changes by the collector to trigger the workflow).

(Eleshia Robinson) #3

Thank you!

The ChangeCount worked great to track when values are changed in the main table.

For example, the main table is UtilityPoles.

Each UtilityPole can have various attachments and also various violations that are stored in different tables named Attachments and Violations with references from each table back to the UtilityPole.

If an attachment for a pole is changed during the QA process, I need the Change counter on the pole to increment.

Any ideas on how to accomplish this?

(Bituminous Roadways) #4

@Eleshia_Robinson hmmmm… you could add the changecounter to your attachments and violations tables, and then on the main table add a virtual column that sums the changecounters for all referenced tables and the main table.

this virtual column would look something like this:

SUM(SELECT(AttachmentsTable[changeCounter],

[REF column to main table] = [_THISROW].[row key])) +

SUM(SELECT(ViolationsTable[changeCounter],

[REF column to main table] = [_THISROW].[row key])) + [MainTableChangeCounter]

Using a virtual column to get the sum of a change column
(Eleshia Robinson) #5

Thank you - I’ll try that out.

(Eleshia Robinson) #6

That worked!

Many thanks!

I altered the virtual field a little to be Yes/No and called it “Changes Made During Review”.

Then I changed the formula to IF(SUM(SELECT(AttachmentsTable[changeCounter],

[REF column to main table] = [_THISROW].[row key])) +

SUM(SELECT(ViolationsTable[changeCounter],

[REF column to main table] = [_THISROW].[row key])) + [MainTableChangeCounter]>0,TRUE,FALSE).

Since I only need to know THAT issues were found during the QA process, this keeps me or anyone else from needing to remember what the count >0 means.

(Bituminous Roadways) #7

Great to hear that worked!