Is it possible to create an Advanced Query across multiple functional tabs (I think the answer is no at least for now) – if not, is this a planned feature?
My use case is trying to create a lot number & expiration date report based on custom fields added to goods receipts and sales invoices, to reconcile how much of a certain chemical is in stock. The lot & expiration are entered on receipt, and on sale, but there is a need to run a report to see if expired chemicals remain (balancing goods received against items sold grouped by item and lot) that need to be pulled/destroyed.
(Just FYI – what else I tried – I tried the transactions view just to make sure, but if I understand the guides etc correctly, there is not a way to access line custom fields from the transactions view. I also tried custom reports, but I know that they are being obsoleted and line custom fields don’t work on the custom reports section.)
Thats possible but you have to go through multiple tabs and run advance query grouped by Lot/no/expiration date. For example go to sales invoice-lines create a query. Go to purchase invoice-lines run the same query. Then you have to manually subtract the numbers. There you get the balance of certain lot.
It wont be that much work if transaction Custom fields are extended to qty ownned/qty on hand of inventory tab. Advance queries are already there. Then there you can get the balance without going through multiple tabs.
Same could be done from Transaction page when custom fields are extended to transactions page but not everyone want their every user to access all transactions. So for this type of query accessing it from inventory is a better option.
@eko Thank you – I actually did search (extensively, across multiple weeks). The post you referenced is helpful, but didn’t directly answer the question. I have also discovered that sometimes if you explain the whole issue, then it turns out there is another way to do it that you didn’t know to search for.
Based on the post you referenced, I’m assuming that the answer would be a) Transactions view is the only way to create an Advanced Query across multiple functional tabs, and b) the Advanced Query for Transactions does not yet support line items?
@Ealfardan Thanks (also @eko re: topic). I had posted as a reply because I was specifically trying to build on the discussion re: lot/serial number, so I thought it was best to keep it in the same topic. If it makes more sense a separate topic, even better.
Hi Eko that would solve the need. I mainly wanted to know if the Advanced Query upgrade was planned because it was posed as the solution to the lot/serial question.