When working with Generic Inquiries...
... there are times when it is necessary to filter on financial period, specifically a range of financial periods.
In the above example, however, if we wanted to show only those PostPeriod between 022019 and 052019:
we would hit a snag.
While this method works for dates, the PostPeriod data is simply six-digit numbers. So, 022019 and 052019 are treated as 22,019 and 52,019 (respectively). If you use this technique, we'll get all values between 22019 and 52019, this would include 032013, 042016, 052018, etc. Obviously, this is not what we want.
So, in this example, what we need to do is to come up with a way for our filter to state: "We want period 022019, 032019, 042019, and 052019". (and it would be really nice if it would work for ANY number of periods).
We *could* simply type the filter we need (if we know how to correctly format it). Thus, we could type:
PostPeriod eq '022019' or PostPeriod eq '032019' or PostPeriod eq '042019' or PostPeriod eq '052019'
This would work, but we would have to re-type it every time we wanted a different range.
So, the first thing we need is a list of the periods.
By putting our start and end periods in cells D2 and D3, we can then use the FINANCIALPERIODLIST function in cell A1 to give us all the periods in that range.
We'll also need to remove the dash "-" from the periods (we can see in the data that the Generic Inquiry leaves it out). This is done by embedding the Velixo function in Excel's SUBSTITUTE function.
Now, we can use the Velixo GIFILTER function. We put whatever other filters we need (in this case we only want non-invoices) along with the name of our period field ("PostPeriod) and a reference to our list of dates in cell A1 (we need to the # at the end of that reference in order to get *all* of the values).
The GIFILTER function automatically formats the filter (including using the correct parentheses) for the values we need:
We'll reference that filter in our GI function and, now, we only get the periods we want:
While not trivial, it's just a few simple functions to format the filter in a way that the Acumatica Generic Inquiry needs.