By using this website, you agree to our Terms of Use (click here)
We have 6 segments in our sub-account structure. When running the predefined Trial Balance Detailed report in GL, we sometimes want to restrict the results to a specific value within an individual segment. I haven't been able to determine the proper definition of a wildcard when setting up an ad hoc filter on the Additional Sort and Filter tab.
For example, assume a company has a subaccount comprised of three segments, each of which has 3 characters. You would then have a structure similar to this XXX-YYY-ZZZ.
XXX - Product Group
YYY - Location
ZZZ - Department
Assume that all three segments contain a valid value of 100 in common.
We would like to run the Trial Balance Detailed report with a filter similar to ???-100-???. This way, we only get back rows for accounts in Location 100.
I have tried using the following wild card definitions without success:
???100???
???-100-???
*100*
***100***
___100___ (I read in help that the _ is a wildcard when creating Ad Hoc filters.)
None of these work when I use "Equals" as the condition. If I use the "Contains" condition, it searches all segments for the significant digits, not just the specific segment identified.
Any ideas?
I've never used the _ in ad hoc filters. Thanks for the tip.
But I just tried _ in conjunction with Contains and it seemed to work.
This was my filter:
Which yields these results:
But that is not the same as this filter:
Which yields these undesirable results:
Tim,
Well, I just tested using the wildcard in a filter in another, newer copy of Acumatica and the filters worked as I had expected. Now I have to put some pressure on the developer of our add-on to be able to move to a newer version of core Acumatica so we can get around this limitation.
BTW, I had already tested using the "Contains" clause without the wildcard placeholders and found the same 'undesirable' results. Running a detail trail balance for a specific segment or combination of segments is dependent on the use of wildcards.
Tim,
The ARM report is an alternative but what we really need is a list of all accounts and subaccounts with an ending balance and related net activity for a specific period. We have a large number of sub-accounts with 5 segments. There are a lot of allocations and cross-departmental billing that needs to be done and these are all dependent on the use of masks on sub-accounts.
Our current plan is to export detail trail balances into Excel and perform allocation calculations there. Once complete, we will return the calculated values back into Acumatica as journal entries.
I am working on a GI that replicates a detailed trail balance so we can pull the data dynamically through OData. Making progress but running into quirks there as well. More soon.
BTW, thank you very much for your input and verification that I was on the right path.
Another thought on this would be to build a simple Trial Balance in ARM. Probably just need one row in the Row Set. Set it to pickup all GL Accounts and set it to Expand.
Then allow the user to enter their wildcard filtering when they run the report.
I just tested and it looks like the ? , and : options work too which allows you to do things like this:
Ah, must have worked for me because I tested on Acumatica 2017 R2.
Hmmm, if you're exporting to Excel anyways, maybe it would work better to pull the data using Velixo Reports.