Skip to Content
0
Former Member
Sep 16, 2011 at 06:59 PM

About FMS query!

44 Views

Hi Forum,

I have a UDF for G/L Account segment in Marketing Docs, especially meant for Purchase Order documents.

When I pick an ITEM/Warehouse Combo on PO order, this item's associated G/L account is fetched at order line level in the field OACT.FormatCode. An example G/L code that got fetched is - 5511-01-11-0000-001 {accountNum(4 chars), company(2), Warehouse (2), CostCenter(4) and Dept(3).

On this line level I have a UDF called CostCenter of size 4 AlphaNumeric

I would like this UDF to get the CostCenter from above G/L Code if G/L Code is present.

I can do that with this FMS query - Select Substring($[OACT.FormatCode.0],12,4)

If the above G/L Account code is empty, then I would like the user to be able to click on CostCenter field and be able to pick from the available CostCenter fields. I can achieve the ability to click on costCenter field and be able to pick through drop down list with this FMS query that works: SELECT T0.[Code], T0.[Name], T0.[ShortName] FROM OASC T0 WHERE T0.[SegmentId] =3

My question is how could I merge the above two queries:

That is be able have a default as in query 1 or be able to override this UDF default entry by clicking on the lens that gives a drop down of values to choose from as in query 2 above.

Thanks.