Original Question:
Hi,
I want to use Id of upserted parent record to related it with its child records, However observed that OpenFn doesn’t return Salesforce Id on the update operation,
As the solution I tried to fetch Id of parent record by the query using external id. I used the same syntax for query as provided in OpenFn documentation but the query returning the result in wrapper format.
Can you please help me with proper query format, To fetch salesforce records in the form of array?
Hard to say if that’s the right implementation plan without knowing the objective of the job, but as for query, you could use the following as a template:
query(SELECT Some_Field__c, Some_Other_Field__c FROM Custom_Object__c WHERE External_Id__c = '${state.data.someExternalId}'),
Hope that’s a good start!
Hi Taylor,
Thanks for your quick response.
Below code we are trying in OpenFn :
// ID_cards_in_big_package receiving from commcare form
console.log(‘1234’+ query(SELECT Id FROM Packet__c WHERE Grand_Packet_extID__c = '${state.data.form.ID_big_package}'));
// Now state has been changed, and we carry on…
steps(
upsert(“Packet__c”, “Grand_Packet_extID__c”, fields(
field(“name”, sourceValue("$.data.form.ID_big_package")),
field(“Grand_Packet_extID__c”, sourceValue("$.data.form.ID_big_package"))
)),
each(
join("$.data.form.ID_cards_in_big_package[*]", “$.references[0].id”, “Id”),
upsert(“Small_Packet__c”, “sp_id__c”, fields(
field(“Grand_Packet__c”, sourceValue("$.data.Id")),
field(“sp_id__c”, sourceValue("$.data.ID_cards_in_big_package"))
))
)
);
And the response we are getting :
function wrapper() {
// Avoid arguments object use disqualifying optimizations by
// converting it to an array before providing it to other functions.
var length = arguments.length,
index = length,
args = Array(length);
while (index–) {
args[index] = arguments[index];
}…
Could you please help us to understand this?
Thank you!
Hi Shraddha,
Remove that console.log(…) that’s outside the query call—you’re logging the function wrapper rather than executing it. This is because all of the operations are wrapped in a promise.
// first execute the query operation…
query(SELECT Id FROM Packet__c WHERE Grand_Packet_extID__c = '${state.data.form.ID_big_package}');
// then execute the steps operation…
// the result of the query will be in state.references[0]
steps(…);
Taylor
Hi Taylor,
I tried the same but I am not getting query result in state.references[0].
The query is :
query(Select Date_Collected__c,Vendor__c From Payment__c Where Date__c = ${state.data.form.deposit_date});
The response is in the below format :
Logging in as eternus@myagro.org.eternusdev.
{ totalSize: 1,
done: true,
records:
[ { attributes: [Object],
Date_Collected__c: ‘2018-12-20T00:00:00.000+0000’,
Vendor__c: ‘a0P1l000000EzreEAC’ } ] }
Finished.
If I try to use state.references[0] the value is undefined.
Please let me know if I am missing on something.
Looks like it could be the same timing issue that Patrick was facing. Can you try wrapping that create in another alterState operation?
alterState(state => {
return query(
Select Date_Collected__c,Vendor__c From Payment__c Where Date__c = ${state.data.form.deposit_date}
)(state)
})
alterState(state => {
console.log(state.references);
return execute(
upsert(“Packet__c”, “Grand_Packet_extID__c”, fields(
field(“name”, sourceValue("$.data.form.ID_big_package")),
field(“Grand_Packet_extID__c”, sourceValue("$.data.form.ID_big_package"))
)),
each(
join("$.data.form.ID_cards_in_big_package[*]", “$.references[0].id”, “Id”),
upsert(“Small_Packet__c”, “sp_id__c”, fields(
field(“Grand_Packet__c”, sourceValue("$.data.Id")),
field(“sp_id__c”, sourceValue("$.data.ID_cards_in_big_package"))
))
)
)(state)
});
Thanks! Taylor
This was helpful. I have resolved the issue in one of the job.
In the above job observed that we get the latest queried result in state.references[0].records[*]. There is one more job in which I have to use two different query results in join operation. Could you please help me to understand how can I achieve this. Below is the code snippet.
alterState(state => {
return query(
Select Id From Bank_Deposit__c Where Name = '${state.data.form.deposit_ref}'
)(state);
});
alterState(state => {
bankId = state.references[0].records[0].Id;
console.log(‘Test==>’);
console.log(bankId);
return query(
Select Id,Date_Collected__c,Vendor__c From Payment__c Where Date__c = ${state.data.form.deposit_date}
)(state);
});
alterState(state => {
return execute(
each(
join("$.references[0].records[*]", “$.references[0].records[0].Id”, “Id”),
update(“Payment__c”, fields(
field(“Id”, dataValue(“Id”)),
field(“Bank_Deposit__c”, dataValue(“Id”)) // Want to use Bank_Deposit__c Id of first query, But Getting Id of Payment__c
))
)
)(state)
});
I’m not entirely sure I understand the desired outcome, but if you want to create a new property in state, you’re free to do so then access it in later operations. Like this:
alterState(state => {
return query(
Select Id From Bank_Deposit__c Where Name = '${state.data.form.deposit_ref}'
)(state);
});
alterState(state => {
bankId = state.references[0].records[0].Id;
console.log(‘Test==>’);
console.log(bankId);
// only state gets passed between operations, so add a property to state if you want to use it later.
state.Shradda = bankId;
return query(
Select Id,Date_Collected__c,Vendor__c From Payment__c Where Date__c = ${state.data.form.deposit_date}
)(state);
});
alterState(state => {
console.log(state.Shraddha);
return execute(
each(
join("$.references[0].records[*]", “$.references[0].records[0].Id”, “Id”),
update(“Payment__c”, fields(
field(“Id”, dataValue(“Id”)),
field(“Bank_Deposit__c”, state.Shraddha Want to use Bank_Deposit__c Id of first query, But Getting Id of Payment__c )
))
)(state)
});
Also, apologies… I can’t totally follow what you’re doing with the “each(join(…))” part. What’s the desired outcome there?
Best,
Taylor
Thanks, Taylor for all your help!
I have resolved the issue with your provided solution. With the use of “each(join(…))” I was trying to join Id of recently upserted bank deposit with each payment record in the array.