06-13-2023 07:30 PM
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])").
Solved! Go to Solution.
06-14-2023 08:16 AM
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])").
06-14-2023 07:31 AM - edited 06-14-2023 07:32 AM
No single quotes are required. And you may need some brackets when doing the expansion in a GetDataBufferUsingFormula API. Here are some examples:
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])")
06-14-2023 08:16 AM
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])").
06-14-2023 11:21 AM
Thanks Eric, removing the square brackets worked.
06-14-2023 12:34 PM
Hi @Yaseen 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.
06-14-2023 01:01 PM
Hi Mike- Yes, works with out single quotes as well. thanks!
06-15-2023 08:31 AM
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.