Skip to main content
Solved

How to display the latest bill number


Forum|alt.badge.img

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

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.  

 

 

View original
Did this topic help you find an answer to your question?

Laura02
Captain II
Forum|alt.badge.img+19
  • Captain II
  • January 22, 2024

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


Forum|alt.badge.img
  • Semi-Pro II
  • January 23, 2024

Hello @Laura02 , thank you for your suggestion. The SUM & COUNT filters are working fine but now stuck with MAX one. So the requirement is the end user is looking to get the data for the year 2022 - for sum & count of payments. Whereas the user also wants the system to display the latest bill number created in the system even if it has been created in Jan-2024. How can I display the bill number in such a case? I tried to create joins but the sum of the payments are getting multiplied by the total number of bills created in the system.

How to get this done?

Thanks in advance.


Laura02
Captain II
Forum|alt.badge.img+19
  • Captain II
  • January 23, 2024

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


bryanb39
Pro II
Forum|alt.badge.img+5
  • Pro II
  • January 23, 2024

@Harry For the requirement of getting the max for all dates, but sum and count for only 2022 you can create a second relationship to the APInvoice.  So add APInvoice again under Tables and then give it an Alias, AllBills for example.  

 



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. 


Forum|alt.badge.img
  • Semi-Pro II
  • January 23, 2024
bryanb39 wrote:

@Harry For the requirement of getting the max for all dates, but sum and count for only 2022 you can create a second relationship to the APInvoice.  So add APInvoice again under Tables and then give it an Alias, AllBills for example.  

 



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 @bryanb39 , for your quick response. So shall i move ahead in adding this relationship in the same GI?


bryanb39
Pro II
Forum|alt.badge.img+5
  • Pro II
  • January 23, 2024

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.  


Forum|alt.badge.img
  • Semi-Pro II
  • January 23, 2024

@bryanb39 ,

So I have given as follows:

Relationships tab

 

Results grid

But still not able to fetch the values. i tried with all the types of joins but it displays the same results.


bryanb39
Pro II
Forum|alt.badge.img+5
  • Pro II
  • January 23, 2024

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.  


bryanb39
Pro II
Forum|alt.badge.img+5
  • Pro II
  • January 23, 2024

Are you not getting any data or the wrong data?  Might be an issue with field format, Max of a string for example.  

 


Forum|alt.badge.img
  • Semi-Pro II
  • January 23, 2024
bryanb39 wrote:

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 @bryanb39 , attaching the GI XML.


bryanb39
Pro II
Forum|alt.badge.img+5
  • Pro II
  • January 23, 2024

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. 

 


Forum|alt.badge.img
  • Semi-Pro II
  • January 23, 2024

Hey @bryanb39 ,

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:

when the Bills part isn’t added, the figures getting displayed here have been verified. Date Range - 012023 to 122023

 

After adding the APInvoice relation

the Bill ref numbers getting displayed here are correct but then the SUM of spend column gets messed up.


bryanb39
Pro II
Forum|alt.badge.img+5
  • Pro II
  • January 23, 2024

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.  

 

 


Forum|alt.badge.img
  • Semi-Pro II
  • January 25, 2024

Hey @bryanb39 ,

The XML file you shared is working as intended. Thank you so much for helping out.

Regards.


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings