Sales Order Query that picks up data from UDF

[former_member607743]

Hi

I hope someone can help me with a problem query.

I need a query that will pick up the sales order value before tax it also needs to show the SO number, Customer name and Code, the user would get a condition to enter the sales order number which is something like 25644 for example to bring up the information. This part is fine and the query is straight forward however, the query also needs to use a UDF called 'Inter Proj Num' which I've placed on the Sales Order screen.

This will have another Sales Order number in this field and links to a sub project that may of been created when a customer wanted an upgrade / addition to the original sales order.

So the query I need is for it to show the sales order info once a number has been placed by the user. If there is a number in the Inter Proj Num field as well it needs to look up that information as well and add that Sales Order amount to the total. As the two numbers will be for the same customer the customer details won't need to be duplicated.

For example:

• Input sales order number e.g. 25455
• Return sales order value where SO number equals 25455
• Return sales order value where Inter Proj. Number is 25455
• Above 2 items can be a total. They need to be exclusive of tax. Need

I hope someone can help me fathom a way of getting this information together, it is much appreciated.

[neilos · Accepted solution]

Hi,

Not sure it can be a total of the values for separate documents listed, but to get a query to show what you've asked, try this;

SELECT T0.[DocNum], T0.[CardCode], T0.[CardName], T0.[DocTotal] - T0.[VatSum] as 'Pre Tax Value', T0.[U_Inter Proj Num] FROM ORDR T0 WHERE T0.[U_U_Inter Proj Num] =[%0] or T0.[DocNum] = [%0]

Though I think you might need to change '[U_Inter Proj Num]' for the UDF title, not the description...

Regards