How can I query a form datasource for a certain value length?
I need to query a field that got exactly 10 numeric charaters. Right now I'm able only to query numeric characters.
this.query().dataSourceTable(tablenum(CustInvoiceJour)).addRange(fieldnum(CustInvoiceJour, VATNum)).value(Global::queryRange('0*', '9*')); this.query().dataSourceTable(tablenum(CustInvoiceJour)).addRange(fieldnum(CustInvoiceJour, VATNum)).value(strLen('%1') == 10);
Sorry, but I know nothing about your form. Check out what query it uses - there may be some extra ranges (such as the range that this thread is about) or joins.
If needed, create a new thread for this other problem and explain it in detail there.
Okay last question. Any hint why the form loads only 1 record while in table browser and sql it selects all?
It ended like this, looks like it works yet still this SysComputedColumn class confuses me too much.
str exp1 = SysComputedColumn::compareExpressions(SysComputedColumnBase::comparisonField(tableStr(KSeF_CustInvoiceJour), identifierStr(CustInvoiceJour), fieldStr(CustInvoiceJour, VATNum)), ' like', reg); ret = strFmt('select top(1) %2 from CustInvoiceJour where %4', CustInvoiceJourDictTable.name(DbBackend::Sql), SysComputedColumnBase::comparisonField(tableStr(KSeF_CustInvoiceJour), identifierStr(CustInvoiceJour), fieldStr(CustInvoiceJour, VATNum)), reg, exp1);
It seems that you still have the same problem because you didn't apply my solution at all. You're still using DictTable.fieldName() instead of SysComputedColumn::comparisonField() and SysComputedColumn::returnField().
You don't have to getting rid of keywords like "select". Don't worry about them.
Sorry yet I'm unable to understand this class fully. Tried to use it's methods yet I don't know how to generate a string like "select field from table where" I was only able to create the second part of comparing yet the same problem with the alias occoured.
public static server str queryVATNum() { SysDictTable CustInvoiceJourDictTable = new SysDictTable(tableNum(CustInvoiceJour)); DictView dictView; str ret; str reg = strFmt("'%1'",(strRep('[0-9]', 10))); dictView = new DictView(tableNum(KSeF_CustInvoiceJour)); str exp1 = SysComputedColumn::compareExpressions(CustInvoiceJourDictTable.fieldName(fieldNum(CustInvoiceJour, VATNum), DbBackend::Sql), 'like', reg); str exp2 = SysComputedColumn::compareExpressions(CustInvoiceJourDictTable.fieldName(fieldNum(CustInvoiceJour, VATNum), DbBackend::Sql), '=', CustInvoiceJourDictTable.fieldName(fieldNum(CustInvoiceJour, VATNum), DbBackend::Sql)); ret = strFmt('select top(1) %2 from %1 where %4', CustInvoiceJourDictTable.name(DbBackend::Sql), CustInvoiceJourDictTable.fieldName(fieldNum(CustInvoiceJour, VATNum), DbBackend::Sql), reg, SysComputedColumnBase::and2(exp1, exp2)); return ret; }
You don't have to (and shouldn't) hard-code the alias, you just need to use the right methods, such as SysComputedColumn::comparisonField() and SysComputedColumn::returnField(). You could also utilize SysComputedColumn::compareExpressions().
By the way, you can simplify your definition of 'reg' with strRep().
I think I finally get it but its a bit messy.
I don't know how to do it without hardcoding the sql statement because it always creates the custinvoicejour table as T1 to it end refering to itself VATNum = VATNum insted of VatNum = t1.Vatnum.
Anything to optimalize this?
public static server str queryVATNum() { SysDictTable CustInvoiceJourDictTable = new SysDictTable(tableNum(CustInvoiceJour)); DictView dictView; str ret; str reg = "'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'"; dictView = new DictView(tableNum(KSeF_CustInvoiceJour)); ret = strFmt('select top(1) %2 from %1 where %2 like %3 and %2 = t1.%2', CustInvoiceJourDictTable.name(DbBackend::Sql), CustInvoiceJourDictTable.fieldName(fieldNum(CustInvoiceJour, VATNum), DbBackend::Sql), reg); return ret; }
After going through the discussion, I did not clearly understand your requirement. Can you explain the requirement again? What exactly you are trying to achieve? Because I hope the issue is resolved for 10 characters string.
You misunderstand the purpose of computed columns. A computed column method should generate T-SQL code to be included in a SQL view. Your code does nothing like that - it's return an empty string.
Before you start writing any X++ code, think about what SQL code you want to generate by X++.
As i further investigated both queries were wrong. As you said my query looked just for first character. The length query also passed string that had more than 10 characters.
So I wanted to try to create a view with a computed field but I did fail again... My code below is always on true I did hope I could use regex here the way I did.
The view is created like this:
SELECT VATNUM, DATAAREAID, PARTITION, RECID, CAST(N'' AS NVARCHAR(20)) AS MYVAT
FROM dbo.CUSTINVOICEJOUR AS T1
public class My_CustInvoiceJour extends common { public static server str queryVATNum() { #define.CompView(My_CustInvoiceJour) #define.CompDS('CustinvoiceJour') #define.VATNum('VATNum') DictView dictView; boolean matched; str ret; dictView = new DictView(tableNum(#CompView)); var VATValue = dictView.computedColumnString(#CompDS, #VATNum, FieldNameGenerationMode::FieldList, true); //SysComputedColumn::returnField(tableStr(#CompView), identifierStr(#CompDS), fieldStr(#CompDS, #VATNum)); str VATNumPattern = @"\d\d\d\d\d\d\d\d\d\d"; System.Text.RegularExpressions.Match myMatch; myMatch = System.Text.RegularExpressions.Regex::Match(VATValue, VATNumPattern); matched = myMatch.get_Success(); if(matched == true) { ret = vatvalue; } else { ret = ""; } return ret; } }
André Arnaud de Cal...
291,969
Super User 2025 Season 1
Martin Dráb
230,842
Most Valuable Professional
nmaenpaa
101,156