Skip to main content

Notifications

Announcements

No record found.

Dynamics 365 Community / Forums / Finance forum / Electronic reporting -...
Finance forum
Unanswered

Electronic reporting - Filter main accounts in export

Posted on by 30
Hi all. In Electronic Reporting, I have a file export which contains ledger transactions. I want to filter the export based on for example a range of main account, e.g. 30000..39999, or for example if the main account starts with either 4, 5, 6 or 7. I've tried different things using a calculated field and the filter function, but I don't get it to work.
 
Can anyone help me out? Thanks in advance.
Categories:
  • Adis Profile Picture
    Adis 256 on at
    Electronic reporting - Filter main accounts in export
    Hey,
     
    It didnt have such a case, so I cant test and check what works, so its hard to tell the exact way how it works, but I would try the following:
     
    1.: You said you could already filter your revenue accounts with STARTSWITH('TR.MainAccount'.MainAccountId, "3"), cant you exclude Main account "39500" with:
    If StartsWith(3) AND(Not((StartsWith(39500))) then StartsWith(3) or something similar?
     
    2.: The SQL query doesnt work as you have strings, therefore, you need to convert to Number. Therefore, I would try using a List(StartsWith(3)) To create a list, convert with INTVALUE() and then Filter or Where should be possible. 
     
    Maybe a developer can help looking at the available functions.
     
    Sorry, if I cant give answers, but I try to give ideas.
     
     
  • tnaess Profile Picture
    tnaess 30 on at
    Electronic reporting - Filter main accounts in export
    Hi Adis. Thank you for your answer. As mentioned, I've tried to filter using a calculated field. I have for example filtered my revenue accounts which starts with the digit 3 by using this formula: 
     
    STARTSWITH('TR.MainAccount'.MainAccountId, "3")
     
    What I'm looking for is something that equals this sql query:
     
    SELECT *
    FROM LedgerTrans
    WHERE MainAccountId BETWEEN 30000 AND 39999
    AND MainAccountId <> 39500;

     
  • Adis Profile Picture
    Adis 256 on at
    Electronic reporting - Filter main accounts in export
    Hey,

    What filter function do you mean?
     
    I would go either to the format or model mapping designer and edit the formula.
     
     
     
     
    I guess you will need on of the logical functions:
     
     
     
    Kind regards, Adis

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 287,696 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,490 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans