Changing data behind the scenes

[addienaon]

Hello,

I’m working on an inspection app. I have setup buttons on each property requested that link to different forms. The forms all have to be separate because of the way the database is setup.

I want to be able to change the status of an inspection to, “Active” , after a note, image, or rating has been summited. The status is in a separate table, having 2 columns: [id] & [Status], that only allows updates.

My Question: Is there a way to update a value in a different table, which would have to link to the same property id, upon saving a form? I don’t want the user to have to see any other views or click any button aside from “Save.”

Any comments are much appreciated!

[Joseph_Seddik]

Why would you want a separate table with just two columns for the status? Why don’t you just add the “Status” column to your existing table? In this way, you just put the relevant expression in “Status” App Formula, and it will update for any row with any data change to this row.

If you’ve separated this single column from the table because you don’t want it to be visible to users, there are ways to simply hide it instead of removing it from the table.

In any case, yes you can use an Action that will trigger on saving the form to update this field in another table.

Have a look at the guide below and if you have any questions the community will surely help!

Actions: The Essentials | AppSheet Help Center

[addienaon]

I agree, it would be easier if the status column were in the same table. Unfortunately, they are already 3 separate tables in the database (Images, Notes, Ratings). There can be multiple Images and Notes for each property, but only 1 rating. So, the Ratings table is set to only allow updates, and the images and notes allow adds. The status column is really a part of the Ratings table, so it can only be updated (I was just trying to simplify the question as best I could). Frankly, I cannot make any changes to the current structure of the db, just trying to work with what we’ve got.

Here is what I’ve tried and where I’ve gotten stuck:

LINKTOROW(ANY(SELECT(dbo.InspexStatusPutView[id],[PropertyInspectionId]=[_THISROW].[PropertyInspectionId])),“Status”)

I can apply this upon saving a form to get it to link to the correct Property_Id, but then I can’t figure out how to pass in the updated status value.

^I also tried this, but I don’t know how to link to the Property_Id. Could it be done with an IF() where the 3 is?

Sorry for the confusion, thanks for any help!

[Joseph_Seddik]

There are more than one way to do that with your existing data structure.

First, I’d say that since each Property can only have one Rating, then Ratings should be part of the Property table, not a separate one.

But in any case, please show us the columns of your current tables and I’ll tell you how you can do it with your existing data structure. Thank you.

[addienaon]

Thank you for your suggestion, but again I misspoke. There actually can be more than one rating for a property if there have been multiple inspections issued for that property. It doesn’t happen often, but that’s why rating is separate from property. Just to clarify, I’ve added the database relationships (rating and status are under the inspection table in the db, although only some columns were loaded as a view into appsheet). There can be multiple notes and images for each inspection.

Notes Table Columns:

Ratings Table Columns:

Image tables:

[Joseph_Seddik]

Thanks for the screenshots.

Note:

I see your tables lack proper references to each other. For example:

1. Notes table is supposed to be related to Property Inspections table. There’s a column “PropertyInsepctionID” in Notes table, but the type is Number and it should be Ref that points to PropertyInspections table.
2. Ratings table has a single Ref column “Rating”. What table is referenced by this column?
3. Similarly, table Images has a PropertyID column of type Number and it should be Ref pointing to Properties tables.

Question:

What are the expected values for column “Status”? In the post you mentioned that it should be “Active”, but its Type is set to Number. Also, what is the logic used to set the values of “Status”? What makes it 1 or 2 or 3 or another value? I’m asking about your logic, just what you want to do, not how to do it. Thanks.

[addienaon]

Thank you!

1. I made the change, Notes table is now properly related to property inspections.

3. I don’t want to load the properties table into appsheet, it’s just on the db. The address is on the inspection view. That would be way too much data to sync.

[addienaon]

Also, here’s the property inspection columns, just to complete the picture.

[Joseph_Seddik]

OK thank you.

For the status table, you should create an action type Data: Set the values of some rows in this table. Let’s name it: statusActive. You should select the “Status” column and set the value to 3.

Now there are two ways to launch this action:

1. As a form save action:

   1. For each of your Ratings, Images and Notes table, create a new action named “launchStatusActive”, type Data: execute an action on a set of rows,
      • choose Status as the referenced table
      • referenced rows should contain: LIST([id].[status]),
        where the id in your form should match the corresponding row’s id in the Status table.
      • select “statusActive” as the referenced action.
   2. In each of the Ratings, Images and Notes views, scroll down to behaviour and select the corresponding “launchStatusActive” action as the form save action.
   3. Please note that this action will be triggered with each form save, whether it was for the addition of a new row, or the edit of an existing one.

2. Through a Bot in Automation:

   1. For each of the Ratings, Images and Notes tables, build a Bot that will trigger on the addition of a new row to the corresponding table.
   2. Create a Run Data Action step,
      • with an action type: Run Action on rows
      • choose Status as the referenced table
      • referenced rows should contain: LIST([id].[status]),
        where the id in your form should match the corresponding row’s id in the Status table.
      • select “statusActive” as the referenced action.

[addienaon]

Excellent! I went with the first method, works perfectly.

Thank you so much for all the help!