Create empty columns in query results #78
Comments
StevenMMortimer
changed the title
StevenMMortimer
added
enhancement
|
Hi @carlganz – Thanks for noting. At the moment if you need all the columns, then use the Bulk APIs. They will return all columns. For the REST and SOAP APIs, I will look into the XML/JSON. I think it's possible because null elements (at least in the SOAP API) come back as Per the Supported Queries Vignette:
|
|
@carlganz I think this can be done with fields on the object. I'd love your feedback on what you might prefer to have returned in a couple more complex scenarios. For example, if a relationship query returns some Contact records with a related Account and some without, then you won't see SOQL: SELECT Id, FirstName, Account.Name FROM Contact {
"attributes": {
"type": "Contact",
"url": "/services/data/v48.0/sobjects/Contact/0033s000013ZGRUAA4"
},
"Id": "0033s000013ZGRUAA4",
"FirstName": "Test",
"Account": null
}XML <records xsi:type="sf:sObject">
<sf:type>Contact</sf:type>
<sf:Id>0033s000013ZGRUAA4</sf:Id>
<sf:Id>0033s000013ZGRUAA4</sf:Id>
<sf:FirstName>Test</sf:FirstName>
<sf:Account xsi:nil="true"/>
</records>In cases where all of the Contact records have a #> # A tibble: 3 x 3
#> Id Account FirstName
#> <chr> <chr> <chr>
#> 1 0033s000013ZPWmAAO NA Bob
#> 2 0033s000013ZP78AAG NA Jill
#> 3 0033s000013ZBSzAAO NA SamIn the more common case, some of the Contact records do not have a #> # A tibble: 3 x 4
#> Id Account FirstName Account.Name
#> <chr> <chr> <chr> <chr>
#> 1 0033s000013ZPWmAAO NA Bob NA
#> 2 0033s000013ZP78AAG NA Jill NA
#> 3 0033s000013ZBSzAAO NA Sam Be$t Account |
|
Ah I see this more complicated than I imagined. For some reason I thought Salesforce API always returned explicit NULL for related fields but as you point out it just gives NULL for the related object. Parsing the SOQL is definitely outside scope so I suspect there isn't a proper solution so feel free to close issue. |
|
@carlganz Thanks for the feedback and looking through the details there. I've grappled with the JSON/XML returned by queries but also the other operations and sometimes I just scratch my head at what the developers were thinking with some of the APIs... I'll keep this open as a reminder to revisit if there ever is a good solution. Dropping the columns has always bothered me, but the workaround I recommend is just checking that the columns exist before further processing query recordsets in your scripts. If it doesn't exist then create and set to |
Issue submission checklist
When filing your issue please make an attempt to understand your query and debug a little bit on your own. Below are a few suggestions on how to troubleshoot and document your issue. You may also refer to the Troubleshooting section of the Supported Queries vignette.
I have set
verbose=TRUEinsf_query().I have tried a few different function call arguments to see if I can workaround and/or isolate the issue (e.g. reviewing the output from the "SOAP" vs "REST" or the "Bulk 1.0" vs "Bulk 2.0" or tinkering with the
controlargument in the function call).I have taken a look at the query unit tests test-query.R to see if my type of query has been documented and tested.
I have considered making a minimal reproducible example using the reprex package. Details on how to create a reprex are available here: https://www.tidyverse.org/help/#reprex.
I have included the version of R and any packages that are used (Hint: Simply copy/paste the result of
devtools::session_info()at the bottom of your issue).Thank you for considering these steps. It will speed up the process of resolving your issue.
Issue description
First of all thank you for the amazing package. In general, null values in Salesforce are converted to NAs in R. However, if a field in a query is null for all returned records the column doesn't appear at all instead of appearing as all NAs.
reprex
Obviously this is contrived example, but in cases where I query a single record I expect to get an explicit NA instead of a missing field when a value is NULL in Salesforce. I'm guessing this issue has something to do with JSON parsing and dplyr::bind_rows
Session Info
The text was updated successfully, but these errors were encountered: