Alternative methods to update relate tables besides action ("Request Entity Too Large" error)

Hey folks, i could really use some help here.

Long story short

i have a photo storage app that contains 2 Google Sheet tables
A “LOCALE” spreadsheet - listing unique names of locations
A “FEED” spreadsheet - listing photo descriptions, including the locations
Both of these tables have a “Location” column, which references each other with a REF column.

Problem is this:
I created an action that, when i edit a location name in the “LOCALE” spreadsheet, it automatically updates all the location names in the “FEED” spreadsheet.

It works fine for most items. However, if i try to edit a LOCALE name that has a lot of entries in the FEED spreadsheet (over 300 or so) i receive a “413 - Request Entity Too Large” error from the Google server, and will not update the name. Troubleshooting indicates i cannot simultaneously update numerous entries without encountering this error.

My question is this: Can anyone recommend an alternative method to update multiple entries other than a custom action (or Google formulas)? This feature is a core feature of my application, no not being to do this severely limits it’s usefulness.

Appreciate any help.

The AppSheet REST API allows you to update a collection of records in a single action.

You can use an AppSheet webhook to invoke the AppSheet REST API. Your webhook would need to include a JSON template that creates the JSON payload that specifies which record need to be updated and their data values.

Having said this, I would first ask myself if there is a database design problem that is making such a large update necessary. Whenever an update to one table requires an update to a whole set of records in a second table, I always ask myself if my database design is wrong. This can happen if I am erroneously repeating data in the second table that should instead by obtained via a reference to the first table or some other table.

In your case, the problem may be that the “Locale Name” is repeated in every record in the “FEED” table. Maybe you should introduce a third table called “FeedLocale” that contains an Id field and a Ref to a record in the Locale table. Your “FEED” would then contain a Ref to the record in the “FeedLocale” table that specifies the “Locale” for the entire set of “FEED” records… If you need to change the “Locale” for all of the related "FEED"s you can simply update the “Locale” value in the “FeedLocale” table.

Adding to Phil’s suggestions about your database design, I’d also make sure you know about the difference between key and label columns. For instance, I would include a hidden uniqueid() key column in your LOCALE table, and set another column (locale name) as the label column. Then in your FEED table, the location column would be a ref-type to the LOCALE table, but the user would only see the value of the label column, not the gibberish key. Also that label column would be editable, and that edit would automatically show in all referenced FEED records, whereas key columns are not editable.

Thanks for answering guys.

Apologies if i seem terse, but neither the labels nor the duplication are not the problem.

To briefly explain, my app records metadata of photos our community takes daily. The reason why the “FEED” has a lot of the same entries is because our community often takes photos in similar locations. So the recurring entries are warranted.

Af far as keys and labels, I am using ref columns. The “LOCALE” sheet has a “ID KEY” which the “FEED” sheet is referencing in each record. Updating the name of the ref column in the app works fine.

The problem is that the “FEED” and “LOCATION” sheet also has a “Location Label” column that records that actual location name the ref column is referencing in the spreadsheet. This lets me read the location label of the “Location” Ref column when viewing the spreadsheet (as the the recorded “LOCALE ID” key column is gibberish).

My questions is based on updating the “Location Label” column in the spreadsheet.

All that said

I have been trying for hours to write a simple webhook script that will update the “Location Label” values in the “FEED” Table when i update the corresponding “Location Label” values in the “LOCATIONS” Table.

But i’ve can’t figure it out. The templates I’ve created do not launch, and every modification I make produces a new error.

This is best i could come up with

{
“Action”: “Edit”,
“Rows”: [
<<Start: SELECT(The Feed_Raw[KEY ID (input sheet)], AND(
IN([AREAS COUNTIES KEY ID],The Locale_Numbers[LOCALE KEY ID]),
NOT(IN([Areas & Counties],The Locale_Numbers[Locale Entries]))
))>>
{ “AREAS COUNTIES KEY ID”: " <<[LOCALE KEY ID]>>"
“Areas & Counties”: “<<[Areas & Counties]>>”
},
<>
]
}

The script (I think) says "when a edit happens in the “LOCALE” table, look for all the rows in the “FEED” table. If a there’s a name in the FEED’s “Area” column that is not in the LOCATION’s “Locale Entry” column, update the “Area” name in the FEED table.

I get a the following error

“Webhook HTTP post request failed with exception {“Message”:“REST API invoke request failed: The HTTP Body which should contain the API Action, Properties, and Row data is missing.”} The remote server returned an error: (400) Bad Request.”

If someone could refer to a sample app that demonstrates the webhook feature (or create an example script for me), that would help a great deal. Please just dont refer me to the webhook pages on the Appsheet site. I’ve already checked those out and I’m still confused because the examples are different than the actual webhook interface.

I just realized what i wrote may seem confusing, so let’s do this

44%20AM

This the row from the “LOCATION” spreadsheet. I just made an update to the label name of one of the entries

35%20AM

This is the label name of the entries in the FEED spreadsheet. They did not get updated.
How should I write the webhook script to ensure the label names stay consistent?

This is because the [locale id] column is set as the key, and ALSO the label for the LOCALE sheet.
Switch the [location label] column in your LOCALE sheet to be the label column (and turn it off for [location id]). Then the [location] column in your FEED sheet should show the readable name, instead of the gibberish key.

image

thanks for replying @Marc_Dillon.

I may be confused, but i don’t think that’s the case. The label and the keys in the app have always been separate.
Screenshot of the “LOCALE” spreadsheet in the app

When i select the “Locale” entry in the “Feed” form on my app, it records the key, not the label. The Key should not be changed or the app won’t find it, which is why i thought it used the gibberish string. I didn’t want to use the name as the key because i may want to edit it.

Did i get all that right? If not, what do i do?
If so, how can resolve my original problem?

this is how the “City of Boston skyline” key is recorded in the FEED spreadsheet. It matches the key from the LOCALE spreadsheet, so i thought it was working as it should. Let me know if i’m wrong

35%20AM

Ok that looks setup right. Just had to make sure. So is your goal just to have the location text in the actual feed spreadsheet (as in your looking directly at the sheet, not from the app)?

You can set an app formula in a real column in your feed sheet to [location].[locale entries]. However it will only update when you create or edit a feed record.

Read up about reference actions (running the same action on all child records). This can be triggered by an update workflow on your locale sheet. The action itself would use the dereference formula from my previous paragraph.

https://www.appsheet.com/samples/This-app-shows-how-to-use-reference-actions?appGuidString=e76d2e73-3d26-475c-a8f8-9911f5015920

Yes exactly, @Marc_Dillon I want the label names to appear next to the key.

I sometimes review the database so having the actual names help.

I’ve done the app formula method, “[location].[locale entries]”. That is what led me to my original problem.

There are cases where i have more than 200 entries that need to be changed simultaneously. However, doing this with the dereferences gives me the “Request Entity Too Large” error. I suspect Google will not allow multiple entries to be changes if there are a lot of entries.

This is why I’m asking for help with webhooks. It seems to be an alternative to using actions and formulas in the app, so i should bypass that error.

So you’ve already tried the reference actions like in the sample app I just linked?

If this is something you just need for viewing in the spreadsheet, maybe you can use a vlookup formula in the spreadsheet?

Yep, I’ve tried it. @Marc_Dillon

here’s the action I created to run when i edit a location field

04%20PM

Here’s is the error

I initially used spreadsheet formulas, but they slowed down the app terribly. I’m using app formulas to keep the spreadsheet formula-free, so it’s as fast as possible.

This is why I’m asking for help with webhooks. The research I’ve done indicates webhooks are the easiest method to update multiple rows simultaneously without slowing down the app or encountering server errors.

Can anyone provide assistance with that?

That’s not the “reference actions” that I’m referring to. Please see the sample app that I posted. That action is fine for the feed records, but then you need another action for the locale records that uses the action type “data: execute an action on a set of rows”.

Yes I can help you with the webhook if nothing else works, but I’m trying to avoid it…heh

Make sure you include the << End >> expression. I see you’ve got “<>”, maybe you had it there?
Also I believe a start expression that is not in a template file will automatically include commas between records. So I think change “},” to just “}”

I already had that set up. That’s this one.

This is the workflow that triggers it

I’m trying to keep this explanation as short as possible to limit complexity. But to be fully transparent, I have a workflow for the LOCALES that triggers if there’s an entry in there that is not in the “PINPOINT” field (I’m using the PINPOINT sheet to validate the different combinations of locations entries)

The workflow triggers the LOCALE actions above, which then triggers the FEED action in my previous post.

To help clarify things:

  1. This method does in fact work with many entries. There are just a few entries that show up on the FEED spreadsheet alot. I assume actions may not have been designed for that edge case so I want to make sure I have a method to address that.

  2. There is a workaround to making the action just appear as a button in the changed field. If the select that button, it does change the FEED entries, but only one by one, which takes over 10 minutes.

So I hope that makes it clear that this method does work as a last resort, but it’s not ideal.

And trust me, i do NOT want to use the webhook either. I prefer to keep everything strictly on Appsheet. But I’ve already spent over a week setting up and troubleshooting different alternatives. It seems there no other method that will ensure all my entries stay updated without severely affect the app performance. So I hope I can finally get some help on that

t

I understand. Try my above suggestions for the webhook and report back. Another option is to use a google script if we can’t get the webhook to work well.

@Marc_Dillon just saw your latest post. I’ll try it out and get back to you.

I’m using google scripts as well (I’ve been at this for a while now)
Webhooks seems like the lesser evil.


I’ll let you know about the webhook @Marc_Dillon

@Marc_Dillon no dice. Here what i got

These are the errors i get.

44%20PM

What am I doing wrong there? It says the API Action, Properties and Rows are missing but i thought the Start string included those??

Looks like you are missing a comma after “<<[LOCALE KEY ID]>>”