Skip to main content

Notifications

Finance | Project Operations, Human Resources, ...
Unanswered

How to query ds for string length

(0) ShareShare
ReportReport
Posted on by 76

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

  • Martin Dráb Profile Picture
    Martin Dráb 230,842 Most Valuable Professional on at
    RE: How to query ds for string length

    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.

  • RadekM Profile Picture
    RadekM 76 on at
    RE: How to query ds for string length

    Okay last question. Any hint why the form loads only 1 record while in table browser and sql it selects all?

  • RadekM Profile Picture
    RadekM 76 on at
    RE: How to query ds for string length

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

  • Martin Dráb Profile Picture
    Martin Dráb 230,842 Most Valuable Professional on at
    RE: How to query ds for string length

    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.

  • RadekM Profile Picture
    RadekM 76 on at
    RE: How to query ds for string length

    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;
        }

  • Martin Dráb Profile Picture
    Martin Dráb 230,842 Most Valuable Professional on at
    RE: How to query ds for string length

    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().

  • RadekM Profile Picture
    RadekM 76 on at
    RE: How to query ds for string length

    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;
        }

  • Bharani Preetham Peraka Profile Picture
    Bharani Preetham Pe... 3,587 Super User 2024 Season 1 on at
    RE: How to query ds for string length

    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.

  • Martin Dráb Profile Picture
    Martin Dráb 230,842 Most Valuable Professional on at
    RE: How to query ds for string length

    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++.

  • RadekM Profile Picture
    RadekM 76 on at
    RE: How to query ds for string length

    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;
        }
    }

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,969 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 230,842 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Product updates

Dynamics 365 release plans