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

1

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”)
)

2

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),

3

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.

4

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
5

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.

6

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?