topic Re: Problem with variables and dynamic API query in Pipelines

Problem with variables and dynamic API query

JayJay11 — Wed, 21 Feb 2024 07:21:14 GMT

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?

Re: Problem with variables and dynamic API query

Anonymous — Wed, 21 Feb 2024 07:34:44 GMT

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.

Re: Problem with variables and dynamic API query

JayJay11 — Wed, 21 Feb 2024 08:03:30 GMT

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?

Re: Problem with variables and dynamic API query

Anonymous — Wed, 21 Feb 2024 09:25:05 GMT

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

Re: Problem with variables and dynamic API query

JayJay11 — Wed, 21 Feb 2024 09:53:57 GMT

Anonymous thank you - actually adding the "@" solved it. Great!

Now only the problem with the datetime. How can I get exactly the format "2024-02-21T00:00:00" as a string starting from the function utcnow()? The pipeline always adds this "Z" at the end, why?

Re: Problem with variables and dynamic API query

Anonymous — Wed, 21 Feb 2024 10:20:38 GMT

Hi JayJay11 ,

@formatDateTime(utcnow(),'yyyy-MM-ddTHH:mm:ss zzz')

Can you check this format and let me know if that works?

Re: Problem with variables and dynamic API query

JayJay11 — Wed, 21 Feb 2024 10:19:04 GMT

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

Re: Problem with variables and dynamic API query

Anonymous — Wed, 21 Feb 2024 10:23:49 GMT

Hi JayJay11 ,

Glad to know that your query got resolved. Please continue using Fabric Community for your further queries.