How can I use AddorModifyRows() to change non-blank data only?

I am currently using the following formula, to first search for an existing order in a table (by matching sender of an order and the order ID), and it creates new rows or modifies old ones no problem.

The issue is if a change to an existing order comes in and it contains only some of the info, so all the existing fields get overwritten with blanks - I’d like for it to make changes only if the parseJSON() returns a non-blank.

The formula in question:

AddOrModifyRows([orders table],Vendor=thisRow.Sender AND [Order ID] = ParseJSON(thisRow.[GPT output], “$.reference_id”)
[orders table].Vendor, thisRow.From,
[orders table].Type, ParseJSON(thisRow.[GPT output], “$.email_type”),
[orders table].Date, ParseJSON(thisRow.[GPT output], “$.pickup_datetime”),
[orders table].[Order ID], ParseJSON(thisRow.[GPT output], “$.reference_id”),
[orders table].[Client Name], ParseJSON(thisRow.[GPT output], “$.client_name”),
[orders table].[Flight number], ParseJSON(thisRow.[GPT output], “$.flight_number”),
[orders table].[Phone Number], ParseJSON(thisRow.[GPT output], “$.client_phone”),
[orders table].[Pickup address], ParseJSON(thisRow.[GPT output], “$.pickup_address”),
[orders table].Destination, ParseJSON(thisRow.[GPT output], “$.dropoff_address”),
[orders table].Notes, ParseJSON(thisRow.[GPT output], “$.client_notes”),
[orders table].Cash, ParseJSON(thisRow.[GPT output], “$.cash_payment”),
[orders table].[Passenger number], ParseJSON(thisRow.[GPT output], “$.passenger_count”),
[orders table].[Car type], ParseJSON(thisRow.[GPT output], “$.car_type”),
[orders table].Class, ParseJSON(thisRow.[GPT output], “$.car_class”)

You could try putting an IF formula into each value. Something like:
[orders table].Type, if(ParseJSON(thisRow.[GPT output].isnotblank, “$.email_type”,Type=currentvalue),

You might need to turn the currentvalue into a lookup:
Lookup([orders table],[Vendor],thisRow[Vendor]).$.email_type

Then your formula for each value would be for example:
[orders table].Type, if(ParseJSON(thisRow.[GPT output].isnotblank, “$.email_type”,
Lookup([orders table],[Vendor],thisRow[Vendor]).$.email_type),

I’ve been trying with the following formula for each piece of information, but it had the same result as my initial formula:

[orders table].Date, IfBlank(ParseJSON(thisRow.[GPT output], “$.pickup_datetime”), [orders table].Filter(Vendor=thisRow.From AND [Order ID]=ParseJSON(thisRow.[GPT output], “$.reference_id”)).Date.First()),

Gonna try testing your idea in the meantime. It just sounds to me like this issue should’ve been tackled many times already and I can’t possibly be the first one looking for such functionality.

It seems that i’ve been able to do it via

[orders table].[Client Name], ifblank(ParseJSON(thisRow.[GPT output], “$.client_name”), [orders table].Filter(Vendor=thisRow.Sender AND [Order ID] = ParseJSON(thisRow.[GPT output], “$.reference_id”)).[Client Name].First()),

It seems too cumbersome to me however, and I don’t have the slightest idea about doc and automation optimization atm. Would anyone have a more elegant solution to this issue?

1 Like

and just a heads up, sometimes(quite often) after it’s done modifying the cells, some of them get square brackets [ ] around the values for no visible reason - breaks cross-table formatting sometimes too. Any idea of what’s up with that?

UPD: I think it might be because I took out the first() from the end of the filter, so it still thinks it’s outputting a whole column instead of a single cell’s value.

Would it at least be possible to add an if condition when choosing which columns to show in the AddorModifyRows() formula? something like if Xvalue.isnotblank(), then put in column#1?

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.