Formatted Search Numbering Wildcards

[Former Member]

I've seen lots of numbers used in formatted search queries on these forums, and it looks like they are used as wildcards (as mentioned below) so that a single query can be used for all marketing documents for instance. Is there a list somewhere that shows which numbers correspond to which tables and fields?

"Note!!! You can replace the $[ORDR.CARDCODE] string (in the query) with the $[$-4.0.0] string, and the $[ORDR.NUMATCARD] string with $[$-14.0.0] string. Using these alternative strings will enable SAP Business One users to activate this query in other marketing documents, by changing only the name of the related table."

My specific question involves a UDF. I have a row level UDF in my marketing documents called "OrderType", which lets us specify if it is a Normal order, Sample order or a Service order. Depending on which is selected, my formatted search automatically changes the G/L Account field and COGS Account field on the same row. Can I use a single query for all marketing documents instead of having one for each table? Below is my query that is currently functional for sales orders.

SELECT 
  CASE $[RDR1.U_OrderType] 
    WHEN 'Normal' THEN 5000
    WHEN 'Sample' THEN 6125
    WHEN 'Service' THEN 5803
  END $[RDR1.U_OrderType] 
FROM RDR1 FOR BROWSE

[Former Member · Accepted solution]

Hi Derek,

You can use:

$[$38.U_OrderType.0] to replace yours.

Thanks,

Gordon

[Former Member · Accepted solution]

That worked in the sales order, but not in the delivery field. How do I make it work across all marketing documents?

Also, is there a list of the table / field numbers for reference?

[Former Member · Accepted solution]

As long as you do not include any table names in your query, it applies to all your marketing documnets. You can find this info by View - System Information.

Actually, you should create another UDF in the item master. The UDF in all your Marketing Row levels will get this info.

Your query will looks like:

SELECT 
  CASE $[$38.U_OrderType.0] 
    WHEN 'Normal' THEN 5000
    WHEN 'Sample' THEN 6125
    WHEN 'Service' THEN 5803
  END

[Former Member · Accepted solution]

I don't want to create a document level UDF because it is possible that one row item is a normal sale item and one row item could be service, so would need to post to different accounts.

[former_member583013 · Accepted solution]

Derek,

You have the exact query from Gordon. A couple of this for your understanding, when you are working with form field values you do not need to use a FROM TableName.

Also, all marketing documents of Item Type have field references for rows as Item $\[$38 and Service Type documents $\[$39

Suda