Forum Discussion
Problem with variables and dynamic API query
Hello all,
so I am trying to use OData with a specific URL to retrieve data from SAP HANA. The URL string that I want to send looks as the following:
$select=CompanyCode,AccountingDocument,Ledger,GLRecordType,ControllingArea,ChartOfAccounts,GLAccount,BusinessTransactionType,CostCenter,ProfitCenter,FunctionalArea,Segment,PartnerCompany,GlobalCurrency,AmountInGlobalCurrency,BaseUnit,Quantity,DebitCreditCode,FiscalPeriod,FiscalYearVariant,FiscalYearPeriod,PostingDate,DocumentDate,AccountingDocumentType,PostingKey,OriginObjectType,GLAccountType,DocumentItemText,Product,Supplier,Customer,OffsettingAccount,WBSElementInternalID,ProjectInternalID&$filter=PostingDate gt datetime'2024-01-31T00:00:00'
When I put this static string in the query box in the copy action, everything works fine.
However, I want to make the filter argument (datetime) dynamic using a variable. To generate the current datetime, I use the activity Set variable and use the following expression:
@formatDateTime(utcnow(), 'yyyy-MM-ddTHH:mm:ss')
The first problem is, that when I run the pipeline and inspect the variable, it is returned as the following:
{
"name": "vDateFrom",
"value": "2024-02-21T07:11:39Z"
}
So the pipeline adds the "Z" at the end, although I clearly stated the shortable format as the desired output. Is this a bug?
Further, how can I use then this variable as the query? So far I am only getting errors. Even when I just try the following:
concat('$select=CompanyCode,AccountingDocument,Ledger,GLRecordType,ControllingArea,ChartOfAccounts,GLAccount,BusinessTransactionType,CostCenter,ProfitCenter,FunctionalArea,Segment,PartnerCompany,GlobalCurrency,AmountInGlobalCurrency,BaseUnit,Quantity,DebitCreditCode,FiscalPeriod,FiscalYearVariant,FiscalYearPeriod,PostingDate,DocumentDate,AccountingDocumentType,PostingKey,OriginObjectType,GLAccountType,DocumentItemText,Product,Supplier,Customer,OffsettingAccount,WBSElementInternalID,ProjectInternalID&$filter=PostingDate gt datetime', '2024-01-31T00:00:00')
Only when I just put the string as stated above (without any functions) the data preview works.
What am I misunderstanding here?
Again, this is not producing what I want, as it is adding things at the end of the string.
I solved it like that:
@concat('&$filter=PostingDate gt datetime''',substring(variables('vReferenceDateTime'), 0, 10), 'T00:00:00''')
7 Replies
Hi JayJay11 ,
Thanks for using Fabric Community.
Can you please try this -@formatDateTime(utcnow(),'yyyy-MM-ddTHH:mm:ss zzz')Output{ "name": "Change Date format", "value": "2024-01-31T22:26:37 +00:00" }
Hope this is helpful. Please let me know incase of further queries.Hi Anonymous thank you for supporting. Though I am not sure how this is solving it, as this is again not the desired format. But let's nevermind that for a moment, I have a general problem understanding how variables work.
I try the following:
So with the first activity, I create a string. That string I store in a variable, which I then want to use in my copy action as following:
This pipeline should only run about 20 seconds, which is the case if I put the string directly to the Query without using a variable. However, when I use a variable, the pipeline runs much much longer, I had to stop it after 15 minutes.
What am I doing wrong here?
Hi JayJay11 ,
I have tried both the ways using Set Variable and normal but didn't find any difference in exceution.
With Set Variable:
Without Set Variable:
Inorder to debug please check the output of Set Variable -Can you please check your syntax?
Correct Syntax:
@concat('sales__1_test','1')
