Skip to main content
Solved

Reference custom field with formula in GI condition


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')

Best answer by Robert Sternberg

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. 

View original

9 replies

dcomerford
Captain I
Forum|alt.badge.img+15
  • Captain I
  • 596 replies
  • January 9, 2024

@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


  • Author
  • Freshman II
  • 10 replies
  • January 9, 2024

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
Captain I
Forum|alt.badge.img+15
  • Captain I
  • 596 replies
  • January 9, 2024

@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)


Robert Sternberg
Captain II
Forum|alt.badge.img+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)

 


  • Author
  • Freshman II
  • 10 replies
  • January 10, 2024

Robert, attached is the xml of the GI.

 


Robert Sternberg
Captain II
Forum|alt.badge.img+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. 

 


  • Author
  • Freshman II
  • 10 replies
  • January 10, 2024

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
Captain II
Forum|alt.badge.img+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. 


  • Author
  • Freshman II
  • 10 replies
  • January 10, 2024

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


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