Skip to Content
avatar image
Former Member

Query: UDO and UDV

Here is the scenario for my client:

I set up 4 UDTs for their freights (one for each location - Table A, Table B, Table C, Table D) which contains the number of Kilometers and the corresponding freight rate for the KM.

(Screen shot 1)

Now on another table where I need to set up per BP, I have columns:

Origin - A, B, C, or D (this corresponds to the location for each table)

To - (Linked to BP Address)

KM - manually input based on the actual distance of the BP address to the selected origin

Rate

Now I wanted to have an FMS where the rate will be automatically computed based on the KM input on the KM column, depending on the origin selected on the Origin column

(Example: If I selected A on Origin column and I input 25 KM, the rate will appear in the Rate column will be the rate on the Table A, If I selected B on Origin column and I input 25 KM, the rate will appear in the Rate column will be the rate on the Table B)

Anyone who can devise me an FMS for this? Thanks!!

screen-shot-1.png (17.9 kB)
screen-shot-2.png (11.7 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Feb 16, 2017 at 01:10 PM

    Hi,

    Did you actually create a separate table for each of the 4 locations (does not seem wise), or do you mean that you have one table where you have entered the 4 locations (is what I would recommend)?

    The FMS query would have to look something like this:

    SELECT ISNULL($[BPFreightTable.U_Origin], 0) * (SELECT U_Rate
                                                    FROM [@LOCATIONS]
                                                    WHERE U_Location = $[BPFreightTable.U_Origin])

    Please note however that currently B1 only allows for one field to trigger the FMS. For example, let us say that the trigger field will be the kilometers. That means that when the user enters a location, and then the kilometers, the FMS would be triggered and the amount calculated, but if the user were to change the location (for example because they accidentally entered it incorrectly the first time) the FMS would not be triggered again, and the amount could be incorrect.

    An idea regarding this shortcoming in B1 has been suggested to SAP on the SAP Idea Place for SAP Business One. Please have look, and vote for it.

    Regards,

    Johan

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Thanks Johan, will revise my tables and try your FMS query. Will give you feedback after I did this.

  • avatar image
    Former Member
    Feb 17, 2017 at 06:18 AM

    I have revised my tables, where I consolidated all the locations in 1 table. (see screen shot A)

    Here is my FMS

    SELECT $[U_Meycauayan] from [@freight_header] a where u_km = $[@BPFreight_Header.u_km] b.U_Origin = 'Meycauayan' LEFT JOIN [BPFREIGHT_HEADER] B on a.U_KM = b.U_KM UNION ALL SELECT $[U_Limay] from [@freight_header] a where u_km = $[@BPFreight_Header.u_km] b.U_Origin = 'Limay' LEFT JOIN [BPFREIGHT_HEADER] B on a.U_KM = b.U_KM UNION ALL SELECT $[U_Cebu] from [@freight_header] a where u_km = $[@BPFreight_Header.u_km] b.U_Origin = 'Cebu' LEFT JOIN [BPFREIGHT_HEADER] B on a.U_KM = b.U_KM UNION ALL SELECT $[U_Tacloban] from [@freight_header] a where u_km = $[@BPFreight_Header.u_km] b.U_Origin = 'Tacloban' LEFT JOIN [BPFREIGHT_HEADER] B on a.U_KM = b.U_KM UNION ALL SELECT $[U_Davao] from [@freight_header] a where u_km = $[@BPFreight_Header.u_km] b.U_Origin = 'Davao' LEFT JOIN [BPFREIGHT_HEADER] B on a.U_KM = b.U_KM

    But this one is wrong and returns error.

    Where is wrong and what would be the correct FMS?

    THanks

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 17, 2017 at 09:33 AM

    Hi,

    Try this,

    SELECT $[U_Meycauayan] from [@freight_header] a where u_km = $[@BPFreight_Header.u_km] = 'Meycauayan' LEFT JOIN [BPFREIGHT_HEADER] B on a.U_KM = b.U_KM

    UNION ALL

    SELECT $[U_Limay] from [@freight_header] a where u_km = $[@BPFreight_Header.u_km] = 'Limay' LEFT JOIN [BPFREIGHT_HEADER] B on a.U_KM = b.U_KM

    UNION ALL

    SELECT $[U_Cebu] from [@freight_header] a where u_km = $[@BPFreight_Header.u_km] = 'Cebu' LEFT JOIN [BPFREIGHT_HEADER] B on a.U_KM = b.U_KM

    UNION ALL

    SELECT $[U_Tacloban] from [@freight_header] a where u_km = $[@BPFreight_Header.u_km] = 'Tacloban' LEFT JOIN [BPFREIGHT_HEADER] B on a.U_KM = b.U_KM

    UNION ALL

    SELECT $[U_Davao] from [@freight_header] a where u_km = $[@BPFreight_Header.u_km] = 'Davao' LEFT JOIN [BPFREIGHT_HEADER] B on a.U_KM = b.U_KM

    Regards,

    Nagarajan

    Add comment
    10|10000 characters needed characters exceeded