About FMS query!

[Former Member]

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.

[Former Member · Accepted solution]

Hi,

Try:

IF $[OACT.Segment_3\] = 0

SELECT T0.Code, T0.Name, T0.ShortName FROM OASC T0 WHERE T0.SegmentId =3

ELSE SELECT $[OACT.Segment_3\]

Thanks,

Gordon

[jitin_chawla]

Hi,

Please also check whether OACT.Segment_3 is 0 or NULL.

If it is 0 or NULL, change the query accordingly.

Kind Regards,

Jitin

SAP Business One Forum Team