Skip to main content
fellow_Ones
Member
Jun 13, 2023 11:30 PM UTC
Solved

Error using Where clause with 'or' condition inside GetDataBufferUsingFormula

Hello -

 

is there a way to make the below work inside GetDataBufferUsingFormula .

Getting Error processing script 'V#Periodic:F#EndBal_Input:A#:U1#USF02'.

Dim SrcDataBuffer As DataBuffer = api.Data.GetDataBufferUsingFormula("FilterMembers(V#Periodic:F#EndBal_Input:S#Actual:T#" & sLastActMth & ":U1#" & mCostCenter.Member.name & ",[A#[PNL].Base.Where((Name StartsWith '4') or (Name StartsWith '5') or (Name StartsWith '7') or (Name StartsWith 'X'))], [U3#[TopUD3].Base],[U2#[TopUD2].Base])").

Looks like not recognizing filter with 'or' inside where clause.

 

 

Below works fine without an 'or' condition.

Dim SrcDataBuffer As DataBuffer = api.Data.GetDataBufferUsingFormula("FilterMembers(V#Periodic:F#EndBal_Input:S#Actual:T#" & sLastActMth & ":U1#" & mCostCenter.Member.name & ",[A#[PNL].Base.Where(Name StartsWith '4')], [U3#[TopUD3].Base],[U2#[TopUD2].Base])").

    EricOsmanskiAnswer
    Employee - Mentor
    Jun 14, 2023 12:16 PM UTC

    Try this removing the square brackets from around the member name, so you don't have 2 sets.

     

    Dim SrcDataBuffer As DataBuffer = api.Data.GetDataBufferUsingFormula("FilterMembers(V#Periodic:F#EndBal_Input:S#Actual:T#" & sLastActMth & ":U1#" & mCostCenter.Member.name & ",[A#PNL.Base.Where((Name StartsWith '4') or (Name StartsWith '5') or (Name StartsWith '7') or (Name StartsWith 'X'))], [U3#TopUD3.Base],[U2#TopUD2.Base])").

    View in thread

    6 replies

    MikeG
    MikeG
    Contributor
    Jun 14, 2023 11:31 AM UTC

     

    No single quotes are required.  And you may need some brackets when doing the expansion in a GetDataBufferUsingFormula API.  Here are some examples:

    MikeG_0-1686741785291.png

     

    I'm not the best coder, but give this a try:

    api.Data.GetDataBufferUsingFormula("FilterMembers(V#Periodic, [F#EndBal_Input], [S#Actual], [T#" & sLastActMth & "], [U1#" & mCostCenter.Member.name & "], [A#[PNL].Base.Where((Name StartsWith 4) or (Name StartsWith 5) or (Name StartsWith 7) or (Name StartsWith X))], [U3#[TopUD3].Base],[U2#[TopUD2].Base])")

     

     

    Employee - Mentor
    EricOsmanskiEmployee - MentorAnswer
    Employee - Mentor
    Jun 14, 2023 12:16 PM UTC

    Try this removing the square brackets from around the member name, so you don't have 2 sets.

     

    Dim SrcDataBuffer As DataBuffer = api.Data.GetDataBufferUsingFormula("FilterMembers(V#Periodic:F#EndBal_Input:S#Actual:T#" & sLastActMth & ":U1#" & mCostCenter.Member.name & ",[A#PNL.Base.Where((Name StartsWith '4') or (Name StartsWith '5') or (Name StartsWith '7') or (Name StartsWith 'X'))], [U3#TopUD3.Base],[U2#TopUD2.Base])").

      fellow_OnesAuthor
      Member
      Jun 14, 2023 3:21 PM UTC

      Thanks Eric, removing the square brackets worked.

        MikeG
        MikeG
        Contributor
        Jun 14, 2023 4:34 PM UTC

        Hi fellow_Ones just for my sanity, can you confirm if in the StartsWith condition that '4' and 4 without the single quotes works?  It won't throw an error, but I believe the quotes are not needed even for a string condition check.

        fellow_OnesAuthor
        Member
        Jun 14, 2023 5:01 PM UTC

        Hi Mike- Yes, works with out single quotes as well. thanks!

        Krishna
        Expert
        Jun 15, 2023 12:31 PM UTC

        1. It should be single code missing verify the string

        2. You can create an account alternative hierarchy and loop through those accounts, you dont have to deal with all the where condition. Just base will work.

        Terms & ConditionsAccessibility statement