Personalized Community is here!
Quickly customize your community to find the content you seek.
Now Available in Community - New TechTalk Videos for 2020
Read More about New TechTalks for 2020
2020 Release Wave 2Discover the latest updates and new features to Dynamics 365 planned through March 2021.
Release overview guides and videos Release Plan | Preview 2020 Release Wave 2 TimelineWatch the 2020 Release Wave 1 virtual launch event
Ace your Dynamics 365 deployment with packaged services delivered by expert consultants. | Explore service offerings
Connect with the ISV success team on the latest roadmap, developer tool for AppSource certification, and ISV community engagements | ISV self-service portal
The FastTrack program is designed to help you accelerate your Dynamics 365 deployment with confidence.
FastTrack Program | Finance TechTalks | Customer Engagement TechTalks | Upcoming TechTalks
I'm trying to set dynamically a value for a QueryBuildRange variable but I got a lot of errors and I'm starting to think that what I want to do is not possible.
This is the code that is the closest I got to work (I don't get an expression error):
value = strFmt('("%1" like "*" + %2.%3 + "*")', any2Str(_compareValue), _tableName, _fieldName);
unfortunately at the time of running the query it gives me :
What are you trying to achieve? Can you give us an example, please?
What's the point of having a literal on the left side of the 'like' operator?
Your code looks like that you're checking if _fieldName contains _compareValue, therefore you could simply use a range:
I'm doing a change to the bank matching rule class.
In this case I need the part after the like to be a field of the query rather than a fixed value, and SysQuery:valueLike only works for a fixed value.. unless I missing something
You use value = strFmt('("%1" like "*" + %2.%3 + "*")', any2Str(_compareValue), _tableName, _fieldName);
Why not fieldStr(_tableName, _fieldName) ? and there is no need to use "+" in the expression.
Can you share more of your code? I only roughly understand your need to check if _compareValue is like the table field value?
Yes, SysQuery:valueLike() works with fixed values and _compareValue is a fixed value, therefore it's applicable.
That you have the field name on the right side of 'like' and the value on the left is a problem that I already pointed out in the previous reply.
What I'm trying to do is to replicate something like this:
select * from [dbo].[BANKACCOUNTTRANS]
where 'Opening Balance' like '%' + [BANKACCOUNTTRANS].[PAYMREFERENCE] + '%'
If I use [BANKACCOUNTTRANS].[PAYMREFERENCE] like '%Opening Balance%' I don't get the results I need.
this code doesn't trigger any error
value = strFmt('("%1" like %2.%3 )', any2Str(_compareValue), _tableName, _fieldName);
but this does :-(
Can you please give us a concrete example of what are you trying to achieve? What values in PaymReference field are you trying to fetch and why?
Also, you didn't tell us what values you have in your variables. One of other potential problems with your code is that you seem to be using a table name, but the data source may have a different name than the table. We can't verify how you fill the variable if you don't show it.
yes it's the data source name, not the table name, this is an example taken using debug:
"(\"255041\" like \"*\" + BankReconciliationDocumentOpenTmp_1.Reference + \"*\")"
the part before the like always works , but is the part after the like that doesn't if i try to add the *
this is the resulting query:
SELECT T1.BANKCURRENCYAMOUNT,T1.BANKDOCUMENTTABLEVIEW,T1.BANKTRANSTYPE,T1.CANCELLED,T1.CORRECTIONBANKCURRENCYAMOUNT,T1.CREDITAMOUNT,T1.DEBITAMOUNT,T1.DESCRIPTION,T1.DOCUMENTNUM,T1.DOCUMENTTYPE,T1.ISMATCHED,T1.ISRECONCILED,T1.POSTED,T1.REFERENCE,T1.RELATEDPARTYACCOUNT,T1.RELATEDPARTYCOMPANY,T1.RELATEDPARTYNAME,T1.RELATEDPARTYTYPE,T1.STATUS,T1.TRANSACTIONCURRENCY,T1.TRANSACTIONCURRENCYAMOUNT,T1.TRANSACTIONDATE,T1.ENDTOENDID,T1.PAYMID,T1.NSLINTEGRATIONUNIQUEID,T1.NSLINTOPERATINGSYSTEMID,T1.RECVERSION,T1.PARTITION,T1.RECID FROM tempdb."DBO".t15348IISMGDEV41412924_E33EF85C8E3B44B59988CDEFA340E440 T1 WHERE (((T1.PARTITION=5637144576) AND (T1.DATAAREAID=N'mmgl')) AND ((((T1.ISMATCHED=?) AND (? LIKE ((?+T1.REFERENCE)+?) ESCAPE '\' )) AND (T1.BANKCURRENCYAMOUNT>=?)) AND (T1.TRANSACTIONDATE<=?))) AND EXISTS (SELECT 'x' FROM tempdb."DBO".t15348IISMGDEV41412924_E33EF85C8E3B44B59988CDEFA340E440 T2 WHERE (((T2.PARTITION=5637144576) AND (T2.DATAAREAID=N'mmgl')) AND ((((T2.BANKCURRENCYAMOUNT<=?) AND (T2.REFERENCE=?)) AND (T2.TRANSACTIONDATE>=?)) AND (T1.RECID=T2.RECID)))) ORDER BY T1.TRANSACTIONDATE
the problem is in this part:
((?+T1.REFERENCE)+?) ESCAPE '\' ))
May I ask for the third time what are you trying to achieve?
Let's forget code for a moment, so it doesn't distract us from more important things.
I'm trying to reverse how the matching rule works, because Description normally contains Reference:
Can you please give us a *concrete* example of what you're trying to fetch?
For example, that you use X as the criteria and you want to find references Y and Z.
an example is a bank statement imported where the field description contains the reference of the bank transaction that needs to match.
like bank statement has description "PARKING EYE REF 371771 083289 CHQ 000802" and bank transaction reference is "000802"
You said that the following T-SQL code works for you:
select * from [dbo].[BANKACCOUNTTRANS]
where 'PARKING EYE REF 371771 083289 CHQ 000802' like '%' + [BANKACCOUNTTRANS].[PAYMREFERENCE] + '%'
but I can't confirm that. It doesn't seem to work. Can you test it once more, please?
I would use this T-SQL code:
select * from BankAccountTrans
where CHARINDEX(PaymReference, 'PARKING EYE REF 371771 083289 CHQ 000802') > 0
When we agree on what T-SQL code we want to generate, we'll do the other step - finding a way how to generate it from X++.
the first query works fine, why shouldn't?
the second one works fine as well
I've been able to set this rang in a query object in VS:
("PARKING EYE REF 371771 083289 CHQ 000802" like "%" + BankAccountTrans.PaymReference + "%")
than I've created a view that uses the query and everything works fine.
So it appears I can't do the same at run time unfortunately
Business Applications communities