Can't get API POST to add records with a reference...

GreenLee

I'm running into some trouble importing some store orders via REST API/Google Apps Script. I have three AppSheet Database tables - Listings, Orders, & Line Items - where Line Items will reference the key values of Listings and Orders. I am able to easily add rows to the Orders table via the API, however when I try to do the same with Line Items' records I get errors thrown in the API audit history.

After some troubleshooting, it seems like the issue is that the Add POST request is expecting the reference value to be equal to the Row ID in the AppSheet Database. However, even though I've set the key columns of each table to their respective unique IDs (Item Number, Order Number, and Line Item ID respectively), the API is still expecting me to use the Row ID value as the reference.

For example, if I have the following row in Orders:

Row ID	Order Number (key column in Appsheet)	Date
eN2p6zyxq84LEYUmxqMDGa	12-34567-89012	04-12-2024

And I try to add the following rows to Line Items via the API (separately):

{
    "Line Item ID":"98765432100",
    "SKU":"ABC",
    "Order Number":"eN2p6zyxq84LEYUmxqMDGa",
    "Quantity":1,
    "Value":"1.99"
}
{
    "Line Item ID":"98765432101",
    "SKU":"ABC",
    "Order Number":"12-34567-89012",
    "Quantity":1,
    "Value":"1.99"
}

The first row would be added to the table just fine, while the second would throw an error (shown below).

Is there any way around this issue, or can it be fixed? The keys I'm using are all unique, and I can't switch to using the Row IDs as references without serious rewriting/inefficiencies. If the issue is with using the AppSheet Database as the backend, would this be solved by switching my databases over to Google Sheets?

For reference, here is the exact error I'm seeing in the Audit Log:

ExceptionHandlerFramework.CustomerFacingException: Invalid request provided to AppSheet database\r\n at DatabaseProviders.TablesProviderUtil.ThrowCustomerFacingExceptionIfUserError(Exception e) in C:\\tmpfs\\appsheet\\Provider\\DatabaseProviders\\Tables\\TablesProviderUtil.cs:line 1332\r\n at DatabaseProviders.TablesDBProvider.InsertRows(Context context, SqlTable table, List`1 rowValues, CancellationToken cancellationToken) in C:\\tmpfs\\appsheet\\Provider\\DatabaseProviders\\Tables\\TablesDBProvider.cs:line 826\r\n at DatabaseProviders.SqlTable.InsertRowsAsync(Context context, Op op, ImmutableArray`1 rows, ImmutableArray`1 colTypes, CancellationToken cancellationToken) in C:\\tmpfs\\appsheet\\Provider\\DatabaseProviders\\Base\\SqlTable.cs:line 225\r\n at ProviderBase.ExternalApiProviderData.AppendRowsAsync(Context context, Op op, PhysicalSchema physicalSchema, ImmutableArray`1 colTypes, List`1 keyPositions, Boolean writeFullTable, Boolean tryUpsert, CancellationToken cancellationToken) in C:\\tmpfs\\appsheet\\Provider\\ProviderBase\\ApiBasedProvider\\ExternalApiProviderData.cs:line 566\r\n at ProviderBase.ExternalApiProviderData.AppendRowsAsync(Context context, Op op, PhysicalSchema physicalSchema, ImmutableArray`1 colTypes, List`1 keyPositions, Boolean tryUpsert, CancellationToken cancellationToken) in C:\\tmpfs\\appsheet\\Provider\\ProviderBase\\ApiBasedProvider\\ExternalApiProviderData.cs:line 211\r\n at Nirvana.Data.DataLayer.AppendTableRowsAsync(Context context, Op op, Int32 ownerId, AppTable appTable, ImmutableArray`1 colTypes, List`1 keyPositions, CancellationToken cancellationToken) in C:\\tmpfs\\appsheet\\V2API\\DataLayer\\DataAccess.cs:line 176\r\n at Nirvana.Internal.InternalAPI.AddTableRowInternalAsync(Context context, Op op, Int32 userId, Int32 executeAs, ChangeEventWorkflowRuleEvaluator changeEventWorkflowRuleEvaluator, CancellationToken cancellationToken) in C:\\tmpfs\\appsheet\\V2API\\InternalAPI\\AddDeleteUpdate.cs:line 774\r\n at Nirvana.Internal.InternalAPI.PerformOperationAsync(Context context, Op op, Int32 userId, String appTemplateVersion, UpdateModeEnum requiredUpdateMode, RowOperationAsync rowOperation, CancellationToken cancellationToken) in C:\\tmpfs\\appsheet\\V2API\\InternalAPI\\AddDeleteUpdate.cs:line 604\r\n at Nirvana.Internal.InternalAPI.AddTableRowAsync(Context context, Op op, Int32 userId, String appTemplateVersion, UserType userType, CancellationToken cancellationToken) in C:\\tmpfs\\appsheet\\V2API\\InternalAPI\\AddDeleteUpdate.cs:line 690\r\n at Nirvana.Internal.RestApiHandler.RestAdd(Context context, IRestApiOp restApiOp, CancellationToken cancellationToken) in C:\\tmpfs\\appsheet\\V2API\\InternalAPI\\RestAPI\\RestApiHandler.cs:line 421

misha_057

You have symbol "-" in Odrer number, maybe problem is there. Try to replace it to "\-".

https://support.google.com/appsheet/answer/10108126?hl=en

GreenLee

Does not seems to fix the issue - still getting error 400: bad request thrown when attempting to pass the order number either with escape characters or without slashes. If I output the order number 123-456-7890 to a separate plain-old text column instead of a reference column, it works without issue (the problem is that the reference doesn't technically exist in that case without additional automation). The POST only seems to work when I use the RowID value that's inherent to AppSheet database as the 'Order Number' reference value, even though it's not set as the key in AppSheet.

As an additional test, I went and copied over the table structures to a Google Sheet, and the POST add request worked just fine (ie. the second row example in my original post). So it seems like the issue is with using the AppSheet Database with a different key value than the default RowID column. Ideally this would be fixed, as I'd like to keep using the database feature.

Can't get API POST to add records with a reference to another table