Electronic reporting - How to use FILTER to filter main accounts on General Journal Account Entry

(0) Share

Report

Posted on by Community Member

Dear all,

I added a record list from the table GeneralJournalAccountEntry and I need to create a calculated filed where I filter the GeneralJournalAccountEntry for a specific Main account, I need to perform this filter inside the model mapping, I cannot use the standard Dynamics filter "Record to include" at running. I also need to use the function FILTER, not WHERE due to performance issues.

I tried to filter on MainAccountID (Main account table) using the relations FILTER(GenJourAccEntry,GenJourAccEntry.'>Relations'.MainAccount.MainAccountId="13000000") but I get the message "Validation error: Invalid comparison condition expression for FILTER. Consider using function WHERE instead of FILTER. See Help page for more information."

Any idea on how I can filter for MainAccount on GeneralJournalAccountEntry?

Thanks for your help

I have the same question (0)

All responses (7)

Answers (1)

Suggested answer

Ludwig Reinhard Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi Mirco,

Instead of

Filter(datasource, xxx = yyy)

use the where function.

Where(datasource, xxx = yyy).

This should make it work.

Just curious: What's the purpose of this report?

Best regards,

Ludwig

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

Hi Ludwig,

WHERE works but the performances are very bad (more than 10 minutes to elaborate) since it first fetches all the records in the tables (and GeneralJournalAccountEntry has a lot of records). I'm looking for a way to use FILTER.

The report must show a list of GL transaction from a few accounts, using a custom made Excel layout.

Was this reply helpful? Yes No
Suggested answer

Laura Manache Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi Mirco,

Thank you for reaching out to Microsoft community. Filter does not support relations in condition and please try to create two datasources and join them:

Please let us know if this helps or if you need any other information, and we will be more than glad to assist further. Also, kindly mark as verified if the answer is helpful.

If there is no helpful information, then please open a Support Ticket with Microsoft to get this investigated further.

With kind thoughts,
Laura

Was this reply helpful? Yes No
Suggested answer

Ludwig Reinhard Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi Mirco,

I see.

What kind of report do you want to realize with that?

It looks as if you are trying to do an accounting report for which we have the financial reporter.

Would that standard reporting tool an option?

Best regards,

Ludwig

Was this reply helpful? Yes No
Verified answer

Ludwig Reinhard Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi Mirco,

Another thing that you can try is the Allitemsquery function.

Might accelerate things.

Best regards,

Ludwig

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

Hello Laura,

I created a Join with GeneralJournalAccountEntry and MainAccount (with MainAccount.RecId=GeneraJournalAccountEntry.MainAccount) but then when I try to use FILTER on the join to filter on MainAccountId (string) I get the same message: "Validation error: The list expression of FILTER function is not queryable"

Was this reply helpful? Yes No
Community Member on at

Like (1)

Report
Copy link

Link copied!

Hi Ludwig,

yes it's an accounting report, however since we need it on a particular Excel layout we decided to use Electronic reporting and not Financial reporter.

I tried ALLITEMSQUERY and it worked! First I created a calculated field using FILTER to filter the accounts I need on MainAccount, then I created another field using ALLITEMSQUERY and i selected GeneralJournalAccountEntry through relation with MainAccount. And I got a GeneralJournalAccountEntry prefiltered on the main accounts that I want.

Thanks Ludwig

1 people found this reply helpful.

Was this reply helpful? Yes No