Solved

Reference custom field with formula in GI condition

  • 9 January 2024
  • 9 replies
  • 95 views
J
Rank

Is it possible to reference a custom field derived from a formula in the conditions of a Generic Inquiry? i have the below formula that will tell me if a sales order is within our capacity or if the order is more than we can handle. How do i reference this in conditions so that the generic inquiry will only show me ones that are over capacity. i can do it with a filter, but i dont know how to have the filter show up when i put the GI on the side bar. We want the GI in the side bar to be empty unless it says “OverCapacity” in the field circled in red when looking at sales orders.

=Switch([EAU.Value]=Null, Null,DateDiff( 'W',[SOOrder.CreatedDateTime],[SOLine.ShipDate])=0,'Due Date is equal to the Creation Date',([SOLine.OrderQty])/DateDiff( 'W',[SOOrder.CreatedDateTime],[SOLine.ShipDate])>([EAU.Value]/50),'OverCapacity', 'Good')

icon

Best answer by Robert Sternberg 10 January 2024, 19:00

View original

9 replies

dcomerford
Rank
Userlevel 7
Badge +12

@justen0351 I dont believe what you are asking for is possible in a GI.

How are your SQL skills as you could use a SQL based view to perform the work to do all the calculations and filtering. Then use this as the basis of the GI

Dermot Comerford
J
Rank

I thought that it wasnt but i wanted to ask and see. i am ok at sql. i have not done a sql based view yet. i was planning on learning how to do it and trying it soon.

 

dcomerford
Rank
Userlevel 7
Badge +12

@justen0351 I use them all the time when it is too complex. Fairly straight forward couple of things to watch out for the CompanyID is important so suggest you display in view and also make sure you use it in the joins. Also watch the IsKey = true in the DAC when you create from view and lastly if the DAC disappears after a publish then restart the application (tip to always do when publishing a site with SQL views)

Dermot Comerford
Robert Sternberg
Rank
Userlevel 7
Badge +8

Would you attach your GI as a XML so we can take a closer look? 

 

It sounds like you need:

  1. A hidden Parameter called “IsSidePanel” with the value “Checkbox” and a default value of Unchecked(False)
  1. Conditions of
    1. IsSidePanel = True AND
    2. ([SOLine.OrderQty])/DateDiff('W',[SOOrder.CreatedDateTime],[SOLine.ShipDate])>([EAU.Value]/50)

 

When you call the GI as a side panel set ISSidePanel to Checked(True)

 

Robert Sternberg, Crestwood Associates -- ** Summit 2024 - Visit Us at Booth #600 ** -- If a community member helped you, please *Like* their post/reply. If they provided an answer to your question, please mark it as *Best Answer*.
J
Rank

Robert, attached is the xml of the GI.

 

1 Attachment
Robert Sternberg
Rank
Userlevel 7
Badge +8

Try the attached version, if it works you will want to hide the IsSidePanel parameter and only pass checked(true) when the GI is functioning as a side panel per your original request. 

 

1 Attachment
Robert Sternberg, Crestwood Associates -- ** Summit 2024 - Visit Us at Booth #600 ** -- If a community member helped you, please *Like* their post/reply. If they provided an answer to your question, please mark it as *Best Answer*.
J
Rank

Robert, it is erroring out because of the > in the condition that uses [true] parameter.

=([SOLine.OrderQty]/DateDiff('W',[SOOrder.CreatedDateTime],[SOLine.ShipDate]))>([EAU.Value]/50)

 

Robert Sternberg
Rank
Userlevel 7
Badge +8

I do not have the EAU attribute so it is difficult for me to test.  Here are some troubleshooting steps. 

  1. Update the Formula on Condition to =IIf(([SOLine.OrderQty]/DateDiff('W',[SOOrder.CreatedDateTime],[SOLine.ShipDate]))>([EAU.Value]/50),True,False)
  2. Update the Formula on the Condition to =IIf(([SOLine.OrderQty]/DateDiff('W',[SOOrder.CreatedDateTime],[SOLine.ShipDate]))>([EAU.Value]/50),1,0)

 

Let us know the results, test after each step to see if the issue is resolved. 

Robert Sternberg, Crestwood Associates -- ** Summit 2024 - Visit Us at Booth #600 ** -- If a community member helped you, please *Like* their post/reply. If they provided an answer to your question, please mark it as *Best Answer*.
J
Rank

Robert, the first one worked perfectly. Thank you very much! I did also try the second on but it did not work.

Reply


Terms & ConditionsCookie settings
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