How can I display the latest bill number for a particular vendor record in vendor GI?
How can I display the total number of payments for the vendors for a particular year in vendor GI?
And how can I display the sum of the spend made to the vendors in a particular year in vendor GI?
Thanks in advance.
Best answer by bryanb39
View original
+18
Hello,
You’ll need to Group by Vendor and use Aggregate Function in the Results tab to achieve each of your requests. Here is an example where I group General Ledger transactions by Branch & Module. and then display the number of transaction lines entered per branch using Count Aggregate function.
Max Bill Number, Count Reference numbers, and Sum payment amounts.
Prompt the User for Begin/End post periods using Parameters tab.
Laura
Hello
How to get this done?
Thanks in advance.
+18
Hello,
Yes if you are reporting payments then the Max Reference number is going to be the Reference number of the Payment, not the bill.
Connect to APAdjust on document Type and Reference Number. Display AdjdRefnbr (Adjusted Reference number is the Bill or Bills). Keep in mind a Payment can pay more than one document and may correctly show duplicates in this case, when the last payment paid more than one bill.
Laura
+5
Then create your relationship with that as well. You may need to play with Inner, Left, Right Joins a bit depending of your other relationships.
Be sure to test the results to make sure your conditions are not cross filtering unexpectedly.
One way I prevent cross filtering is to add Data Ranges in your relationships themselves for the Sum/Count Table including Parameters and Date Functions. You’d put start and end dates in the parent field Greater Than or equal to the Tran Date in the child.
An alternate method is to not create the second relationship and use iif Statements. If X is between dates, then return 1 else 0 and then sum the result to get a count. Same for the sum, if X is >= start date and X is <= end date then return amount else 0, sum the result.
Then you can get max from this same table.
Then create your relationship with that as well. You may need to play with Inner, Left, Right Joins a bit depending of your other relationships.
Be sure to test the results to make sure your conditions are not cross filtering unexpectedly.
One way I prevent cross filtering is to add Data Ranges in your relationships themselves for the Sum/Count Table including Parameters and Date Functions. You’d put start and end dates in the parent field Greater Than or equal to the Tran Date in the child.
An alternate method is to not create the second relationship and use iif Statements. If X is between dates, then return 1 else 0 and then sum the result to get a count. Same for the sum, if X is >= start date and X is <= end date then return amount else 0, sum the result.
Then you can get max from this same table.
Hello,
Thank you
+5
Yes in the same GI. If you run into issues, you can instead create the formulas described at the end my post. I’m not sure which method will run faster, but sounds like performance isn’t the main concern for this GI.
So I have given as follows:
But still not able to fetch the values. i tried with all the types of joins but it displays the same results.
+5
Can you export the GI as an XML and post it here?
What are the Conditions set to?
You probably want Payments Left Join to APInvoice 1st Table. Add the date range in the relationship to be the year you want Sum and Count for.
Payments Left to APInvoice (Bills Alias) 2nd Table
Condition would be Payment Date is greater than Start of your the year you are looking at.
+5
Are you not getting any data or the wrong data? Might be an issue with field format, Max of a string for example.
Can you export the GI as an XML and post it here?
What are the Conditions set to?
You probably want Payments Left Join to APInvoice 1st Table. Add the date range in the relationship to be the year you want Sum and Count for.
Payments Left to APInvoice (Bills Alias) 2nd Table
Condition would be Payment Date is greater than Start of your the year you are looking at.
hey
+5
The main culprit was the condition filtering the GI to just last year. You’ll want the date range in the relationship of the table returning just last year.
Left Join Vendor to the date filtered table.
Inner Join Vendor with the Bill Table that will get Max.
I added a couple field for validation to show the ranges are different.
Updated XML is attached.
Hey
Thank you for your such kind help, I imported the XML, but it still isn’t displaying the correct result. It displays the correct info about the latest bill number, but then it is messing up the SUM of the payments part, that have been facing already. I have shared the screenshots below:
the Bill ref numbers getting displayed here are correct but then the SUM of spend column gets messed up.
+5
Oh yeah, because we used 2 different tables it created a many to one relationship and multiplied it by the number of Bills for that Vendor.
I created a relationship from the Bill to the APAdjustment table instead and used the application amount.
I removed your check Filter and used a formula to return the Application amount if it’s a check. Same for the count.
This should prevent double counting. Please confirm.
It does however assume you applied every payment, but considering this is Vendors I would hope that’s a safe assumption. It could be more of an issue for Receivables.
Hey
The XML file you shared is working as intended. Thank you so much for helping out.
Regards.
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.
