Query: UDO and UDV

[former_member390199]

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!!

[kothandaraman_nagarajan]

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

[former_member390199]

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

[Johan_H]

Hi,

Can you please explain what this query is supposed to do? Is this a report, or is this for your formatted search?

Regards,

Johan

[Johan_H]

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 Bus.... Please have look, and vote for it.

Regards,

Johan

[former_member390199]

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