This feels pretty advanced, but maybe I'm missing something simple and hopefully someone here can point me in the right direction. It's complicated as it's dealing with line items and the order of the line items matter. There's almost two questions in here:
In a list (line items) Zapier ignores null values even when given to a code step how can I override this to have it output an empty value in a line item?
How do you specify the value from the 3rd line item when dealing with a list (line items)?
So here's what I'm getting from an API query to Quickbooks
There are 4 variables for each Custom Field
ID
Name
Value
Type
In the end I need to put the value for the PO Number custom field in one place, the value in the Sales Rep field in an other place and use the In Hands date in yet a third place. They always come in the same order, but most often only one field is filled in. As you can see Zapier sees the null value for P.O.Number but when I use the Estimate Custom Field String Value Elsewhere in the zap it only sees two values.
So I tried doing a line itemizer step:
But the output has this as the first line item (the value should be null, empty or "")
and of course in the second line item the value is the date
and in the third line item the value is null/empty
So I have no idea how to get it to line up correctly.
I've tried the following:
Converting it to text (a string) from a line-item and I just get "Landa,02/19/2020" If there were a leading comma I could deal with that but nope.
Sending it to a code step but the code step splits it on a comma and I only get two values. (aka it only finds one comma)
I want to have a way to grab the first value (even if null) the second value (even if null and even if the first value was null) and the third value (even if null and even if one or both of the first two values were null).
Got any Ideas?
Page 1 / 1
ugh, you found a major pain point for me @PaulKortman .
In working with our Airtable connector where we built in line-item support for Airtable, this was such a pain to deal with because it was pretty common for airtable records to have null values.
So, we had to come up with a work around where we would insert a placeholder for null values (a string like __EMPTY__ ) so that it would show up, then when performing any update, inserts, we would convert that placeholder back into an empty string before updating.
But that was done within the connector code, so not really possible to do via a code step or formatter.
The good news is, I believe* they fixed the bug that created this issue within the development environment. But, it requires the connector to be rewritten to take advantage of the new approach they made available.
So, whoever is the owner of the Quickbooks app should be able to rewrite it in a way that would fix this issue, but it may cause issues with existing zaps, so likely why they haven't modified it yet (or just not a priority for them).
*zapier will need to confirm if this is true or not, but from my experience it appears this way
@Openside I'm using a custom built app for Quickbooks because the normal Quickbooks app doesn't have the query functionality I needed for this zap.
So I'm in charge and can modify the connector code. What I don't know if how to take advantage of the new approach... do you have any more information on this? I'd love to fix it at that level!
are you returning the line item data as an array of objects? or as comma seperated strings ?
(im beginning to question if it has indeed been fixed - the thing im thinking may not be the same cause of what your experiencing)
I didn't modify the default code very much, so here's what I have for the returning data (in fact I think this is just the default for the Zapier platform):
return z.request(options)
.then((response) => {
response.throwForStatus();
const results = z.JSON.parse(response.content);
return rresults];
});
any idea what the results looks like? is it an array? or an object with an inner array inside of it?
Here's a sample of results. It's stripped of private information but the format is the same.
{
"Estimate": {
"domain": "QBO",
"sparse": false,
"Id": "3055",
"SyncToken": "1",
"MetaData": {
"CreateTime": "2019-12-30T06:46:03-08:00",
"LastUpdatedTime": "2019-12-30T10:58:41-08:00"
},
"CustomField": t{
"DefinitionId": "1",
"Name": "P.O. Number",
"Type": "StringType"
}, {
"DefinitionId": "2",
"Name": "Sales Rep",
"Type": "StringType",
"StringValue": "Landa"
}, {
"DefinitionId": "3",
"Name": "In Hands Date",
"Type": "StringType",
"StringValue": "02/19/2020"
}],
"DocNumber": "98565",
"TxnDate": "2019-12-30",
"CurrencyRef": {
"value": "USD",
"name": "United States Dollar"
},
"TxnStatus": "Closed",
"LinkedTxn": {
"TxnId": "3078",
"TxnType": "Invoice"
}],
"Line": {
"Id": "1",
"LineNum": 1,
"Description": "Pricing - Drinkware ",
"Amount": 362.08,
"DetailType": "SalesItemLineDetail",
"SalesItemLineDetail": {
"ItemRef": {
"value": "65",
"name": "Tumbler:20 Oz"
},
"UnitPrice": 1.99,
"Qty": 12,
"ItemAccountRef": {
"value": "13",
"name": "Tumbler Income"
},
"TaxCodeRef": {
"value": "NON"
}
}
}, {
"Id": "2",
"LineNum": 2,
"Amount": 7.00,
"DetailType": "SalesItemLineDetail",
"SalesItemLineDetail": {
"ItemRef": {
"value": "7",
"name": "Set Up Fee"
},
"UnitPrice": 7,
"Qty": 1,
"ItemAccountRef": {
"value": "23",
"name": "Tumbler Income"
},
"TaxCodeRef": {
"value": "NON"
}
}
}, {
"Amount": 337.08,
"DetailType": "SubTotalLineDetail",
"SubTotalLineDetail": {}
}, {
"Amount": 9.42,
"DetailType": "SalesItemLineDetail",
"SalesItemLineDetail": {
"ItemRef": {
"value": "SHIPPING_ITEM_ID"
}
}
}],
"TxnTaxDetail": {
"TotalTax": 0
},
"CustomerRef": {
"value": "531",
"name": "CT733- ACME Corporation"
},
"CustomerMemo": {
"value": "TEXT
},
"BillAddr": {
"Id": "1917",
"Line1": "555-555-1212",
"Line2": "100 Pleasant Dr.",
"City": "Somewhere",
"CountrySubDivisionCode": "SC",
"PostalCode": "50407"
},
"ShipAddr": {
"Id": "1918",
"Line1": "Fred Smith",
"Line2": "CT733- ACME Corporation",
"Line3": "555-555-1212",
"Line4": "100 Pleasant Dr",
"Line5": "Somewhere, SC 50407"
},
"FreeFormAddress": true,
"TotalAmt": 343.50,
"ApplyTaxAfterDiscount": false,
"PrintStatus": "NeedToPrint",
"EmailStatus": "EmailSent",
"BillEmail": {
"Address": "fred.smith@acme.com"
},
"DeliveryInfo": {
"DeliveryType": "Email",
"DeliveryTime": "2019-12-30T06:46:05-08:00"
}
},
"time": "2019-12-31T08:48:48.955-08:00"
}
Estimate.CustomField.forEach(function(obj){
if(!(obj.hasOwnProperty('StringValue'))) {
obj.StringValue = " ";
}
})
Which I can read/understand. However, I need a bit more help as the Estimate is not a variable, the variable is results from this line: const results = z.JSON.parse(response.content); So I would think I need to do results.Estimate.CustomField instead of just Estimate.CustomField. But I'm not sure if that's correct syntax. I would love any assistance on this. perhaps @Openside or @ikbelkirasan might have some insight here? (Thanks for your help!)
I also noticed that in the current code results is a const, so I couldn't actually edit/add a property to the object within there. I could just switch it to var right?
yeah, they're suggestion looks correct but its not meant to be exact code, just psuedo code to point you in right direction, since CustomField is an array, you would need to loop through it like:
for( var cf in results.Estimate.CustomField ){
//add their code to check if no value for StringValue
}
and yes, if you change results from const to let or var it should work
Hi @PaulKortman - I think the issue with this one is that Value key in not defined in this object. So it's not really an issue with its value being null
I've created a test app for this one and I can confirm that Zapier does show null values. (See below)
So in order to make sure each object of the array has the Value key, you can use the following code snippet. It basically sets the default value for Value to null , so if Value doesn't exist in obj , it will be added in the returned object.
Thanks everyone for your help. In the end this is the full code I used to accomplish it. I could have used anything in place of " " but decided that was fine to use.
tree, so I actually have the values of the customfields mapped to their own spot like results.Estimate.CSR or results.Estimate.PONumber etc. Much easier to use this way!
I posted the code back here for anyone else who might run into this in the future.
Hey all, I was directed to this thread from another question and thought I'd chime in with a few clarifications. Line items in Zapier are a complex topic because our handling of them has changed over the years as we've learned better patterns. Thus the particular apps being used can impact the behavior you see. Our new platform should have the kinks worked out though. Here is a quick run through of what two apps, both built on the Developer Platform, do with line items that are missing data.
I have an app with a trigger that generates line items using this code:
var id = Math.floor(Math.random() * Math.floor(1000));
// Notice that the second item defines price as null, and as missing quant completely
I have a second app with an action that defines a line item group with "Desc" and "Quantity" fields. When I map data into the Zap, it might look like we are passing CSV strings into the action, but we really aren't.
We'll provide the app with the full structure of an array of objects. You can confirm this by looking at the sample of the action to see what was created.
Note how the second item in the list (denoted as "1"), is missing quantity because that value was null from the trigger.
@PaulKortman@cooksey or anyone else.
Is there a way to execute the solution @PaulKortman came up with in the Code by Zapier app?
I have the exact same problem that @PaulKortman had however I'm not building an App i'm using the "Quickbooks Look up invoice" Zapier step. When I referenced the output of that for my next steps I'm finding that empty custom fields are just not showing up - just like in @PaulKortman 's examples above.
I tried executing the solution verbatim in a Code by Zapier step but got the 'ReferenceError: z is not defined'. I realized this was because this thread is referencing running Javascript on the backend through a custom app build through the Zapier Developer Platform rather than running code in a Zap.
For reference here is my starting point:
Note I don't have any real experience coding in Javascript.
I’m also having an issue with running the javascript as @Patrick_stinus did above -- I still don’t understand how values are not lining up as key values…
@mindlash this won’t be a technical answer but it might help.
first an attempt at answering your question (if this doesn’t make sense don’t worry - you don’t need to get it for the next part to be helpful). it isn’t showing up because the values are stored in a Json Database Structure rather than a Normalized Tab Delimited structure. So the difference between a standard text doc with sentences written on it and a csv comma delimited standard structure with data in it - they both import into excel but the text file would be all over the place and not useable while the CSV would fall cleanly and repeatably into rows and columns. Why? Because of the comma in a CSV that tells excel when a new row or column should start. Json is text based data structure with no built in guard rails saying how it should be expressed in table formate - the data we typically see come into Zapier is in a more typical SQL format which does have said structure built in.
To put in another way the variables are stacked like 3 pancakes one on top of the other rather than like 3 toys on a 3 shelf bookshelf. If you remove pancake 2 there is no way of knowing pancake 3 is actually pancake 3. It could just as well be 2 of 2. Where as if you remove the toy from shelf 2 it’s clear there are 3 openings and the toy on the top is the 3rd shelf.
The way zapier works is you build around expected outcomes. So “the data on the 2nd shelf” = phone number. If 2nd shelf is empty no worries because a standard SQL structure will display shelf 2 as empty. Json breaks all of that in zapier because where phone number is going to end up in a table that you drop the data into without special instructions first is completely dependent on the existence or null existence of the other values. The only case in which Json is predictable is if you have 100% of your fields for 100% of your items NOT Null (meaning they have some value even if that value is the text reading “n/a”
That brings me to the solution that has worked for me. Every new invoice that I create, the second I create it, I have a zap that runs which assigns some value to every custom field. So that means for me some fields get “n/a” to start and might get updated later with values.
(note: this works because the “update invoice” zap doesn’t suffer from the problem “look up invoice” does because it isn’t reliant on what is currently in the custom fields so the Json doesn’t screw it up. It’s basically saying “what do you want me to put for pancake 1, 2 and 3 which is different than what is pancake 1,2 and 3)
this is an inelegant solution but amazingly it has worked for 100% of my invoice for the past couple of months for me.
hope this helps shed some light on what is happening and provides you with a possible fix.
also - I hope someone more knowledgeable than I jumps in and shares how this could be done properly. I’m convinced there is a line of clever code that could be dropped into Zapier (not on the dev side but in the prebuilt module side “run java script” zap we both referenced) that could help normalized Json into tables.
best of luck
Thanks for the very thorough explanation of where the problem lie @Patrick_stinus - It’s much appreciated. I’m curious of a couple of things now based on the suggested bandaid.
Do you have an example of how to fill these orders with ‘n/a’ by way of zapier? I’m honestly not familiar with just how many additional fields are in these metadata values/key pairs.
This would need to run prior to my other zaps that pull the info, so would this be a stand-alone zap? And, if so, how would I ensure THAT zap gets run first vs the others? (or, is this something that needs to be included in front of the zap itself.
And a more opportunistic question: Since I have the legacy free account, I get no support -- would buying a 1-month level gain me access to support that could help me with this zap/config ?
Seems like a lot of work to get something working, that was previously working just fine until OM4 developers of the Wordpress Zapier made a change. I previously was able to simply choose ‘Gamer Tag’ from the drop-down (now grey’ed out, see ref image).
Thanks again for the feedback.
1 sure here you go:
Yes you can put this in line with another zap if you wanted
The upgrade you are talking about isn’t going to get you the support you need. Is it worth figuring this out? The ideas here are fundamental to understanding how databases work. I would say struggling through your problem is worth its weight in gold in life long learning.
Hi @PaulKortman, @mindlash, @Patrick_stinus! I wanted to pop back in and update the thread with a solution my teammate Leo recently found. I hope this helps!
What you can do is use a Formatter > Numbers > Spreadsheet-Style Formula step. Have a look at : and
Formula that was used in these screenshots: IF("{{106823975__line_items_]name}}"="","value","{{106823975__line_items_]name}}")
There are 4 variables for each Custom Field
But the output has this as the first line item (the value should be null, empty or "") 
and of course in the second line item the value is the date
and in the third line item the value is null/empty
So I have no idea how to get it to line up correctly. 
I've created a test app for this one and I can confirm that Zapier does show null values. (See below)
Note I don't have any real experience coding in Javascript.
