Building a Query that will return Project Distributions

@Tyler Lester
in output, go to account distribution section. it has other fields like the projects, t codes , etc.

@Tyler Lester, Not sure if you were trying to build the query in webview, but if that's the case, the project distribution is not quite available on new queries in webview. There's a message on that area for an Invoice query under Invoice account distribution > Project ID that states ‘Support for this field will be available in a future release.’

In database view, however, this is still accessible – you just have to remember that the query will produce a line for each line of distribution so it will show the same invoice for however many lines of distribution there are. You'd want to create an Invoice (not vendor) query, set whatever criteria you want for the invoices you want to see (for example, invoice status = pending). Then move to the Output tab and expand on ‘Invoice account distribution’ and you'd want to include Account number, amount, and/or percent and then you'd also want to select Project ID and another amount/percent. Your results will look like my screenshot (results tab) for an invoice with multiple lines of distribution – in this example some of the lines have a Project distribution and some don't but the total equals out to the amount of the invoice.

So in just focusing on the expense side (could filter to the type of account I want to see if you don't want to see both the lines of liability and expense) in my screenshot. You'll see Invoice #0707 with a total of $150. Account 01-5250-00 for $25 hitting project ID ‘Art Club’, account 01-5000-00 for $50 hitting project ID 1001, and 01-5200-00 for $75 hitting project ID 1002. In my sample data, projects are not required on the liability side, so again, I could filter to only include the expense type if this creates too much to filter through. Hope this helps but let me know if you have questions!

@Tyler Lester, yeah the query is definitely going to show a different line for each change in the distribution rather than summarizing it like you have written out. I'm thinking you'd need more than one addition of the amount/percent listed under the Project ID to show if there are several projects associated with one account line of distribution. For example, in looking at the previous screenshots I shared, you'd want to have the account distribution listed so ‘account’ and amount/percent and then select project ID/amount multiple times to showcase any projects distributed to the same account line of distribution. That would show as amount, amount (1), amount (2) etc. so that you know it's a different line of distribution. As a query, it won't summarize that information to sort of wrap up into the full amount, though, and will show them on separate lines as if they're duplicated.

I was checking out a report that might help you get this summarized view, though, instead of doing the query which will always duplicate the invoice line per change in distribution. With the Invoice expense allocation report, you can select on the Content section to ‘Show distribution for these characteristics’ and select Project ID. So when the invoice in my example above has the same account for a total of $400, but it's split 50/50 between two projects, this view on the report showcases the full distribution with the account number repeated and the different project IDs it was distributed to.

Let me know if I'm misunderstanding your goal here or if you have additional questions!