Solved

Generic Inquiry - Can do the calculation?


Userlevel 3
Badge
  • Jr Varsity II
  • 103 replies

Hi Everyone

Currently , we have create a generic inquiry to combine table invoice and memo  and table payment and check  in order to get the column for “ due date “ and “ payment date”  

 

If we would like to get system to calculate the day of over due , which mean “due date” minus “ payment date” in order to get the day of over due.  May i know how to do this in generic inquiry? 

 

Hope to hear from you soon.
 

icon

Best answer by Naveen Boga 15 September 2021, 18:08

View original

20 replies

Userlevel 7
Badge +17

Hi @erin  Please try with the below formula.

=  DateDiff( 'd', [ARInvoice.DocDate], Today() )

 

 

Userlevel 7
Badge +17

Hi @erin  You can use the DateDiff function to get the differences between the 2 dates. Please find the screenshots for references. (screenshots from other forum)

 

Hope this helps!!

 

 

 

Userlevel 7
Badge +17

Hi @erin  Modified the provided GI, and I’m highlighting the records with Yellow color in the GI if OverDue is more than 20 days. Please find the screenshot and the GI. 

 

Userlevel 3
Badge

Hi @erin  You can use the DateDiff function to get the differences between the 2 dates. Please find the screenshots for references. (screenshots from other forum)

 

Hope this helps!!

 

 

 

Hi Naveen

If we want calculate the invoice due date and today date so that we know the day of over due, what is the formula that we need enter in generic inquiry-result grid? DateDiff(‘d”[?????],[????])

Hi,

Can anyone help me with this question above?

Thank you.

Userlevel 3
Badge

HI Naveen

 

Got it ! thank you very much

Userlevel 7
Badge +17

@erin Glad to know that you can able to do the calculation in GI. :)

Userlevel 3
Badge

Hi @erin  I don’t think that is possible in standard Acumatica.

 

Hi Naveen

Thanks for your reply and noted.

By the way, is there any way that highlight the font in different color to differentiate and highlight us for those long over due invoice , example more than 20 days, system can highlight the color in RED?

Userlevel 7
Badge +17

Hi @erin,

Yes, we can highlight the records based on the condition.

I just did a sample example to highlight with RED color for the OPEN status Sales Orders. Please find the screenshot for reference.

 

 

Userlevel 3
Badge

Hi @erin,

Yes, we can highlight the records based on the condition.

I just did a sample example to highlight with RED color for the OPEN status Sales Orders. Please find the screenshot for reference.

 

 

Great! Can you guide me how to do that? 

 

Userlevel 3
Badge

Hi @erin,

Yes, we can highlight the records based on the condition.

I just did a sample example to highlight with RED color for the OPEN status Sales Orders. Please find the screenshot for reference.

 

 

Great! Can you guide me how to do that? 

 

This is generic inquiry , not sure if it is workable? as we use formula to count out the over due date and we would like highlight those over due date is over 20 

 

 

Userlevel 7
Badge +17

@erin  Can you please attach your Generic Inquiry here.

Hi Naveen,

I have added row conditional highlighting to an inquiry, which works well. I also added column conditional highlighting, but now the row conditional highlighting gets stopped on that entire column accept where that specific row in the column is highlighted. Is there a way to prevent that from happening?

Thank you.

 

Userlevel 3
Badge

Hi Naveen

Thank you very much!!!

By the way, not sure if we can copy the existing generic inquiry into another one and named in different title?

Userlevel 7
Badge +17

Yes… we can do copy and paste it with different title..

  • Copy the existing GI
  • Click on new GI (+) button
  • Provide new GI Title
  • Click on the Paste button

Userlevel 3
Badge

@erin  Can you please attach your Generic Inquiry here.

Hi Naveen

 

Please confirm if the file is your referring to?

Userlevel 3
Badge

Hi @erin  Modified the provided GI, and I’m highlighting the records with Yellow color in the GI if OverDue is more than 20 days. Please find the screenshot and the GI. 

 

 

Hi Naveen 

Thank you very much ! this is exactly what we need .

However , can you based on the column “ Day of Overdue” instead of “ over due”?

Do you mean i need to upload your revised GI that you attached? How to do it? can you guide me for it?

Not sure if you able to guide me where to change in GI so that i can edit by myself as i wish 

Thank you
 

Userlevel 7
Badge +17

Hi @erin  Modified the GI to the “Day of Overdue” . Please find the attached GI.

 

Userlevel 3
Badge

@erin Glad to know that you can able to do the calculation in GI. :)

 

Hi Naveen

A quick check , the result of calculation is in “font” type instead of “number” so that we cannot use the filter to filter the number from ascending or descending .Not sure if there is any way change to “ number” type?

Userlevel 7
Badge +17

Hi @erin  I don’t think that is possible in standard Acumatica.

Reply


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved