REF - Dropdown - Dynamic additional Values

Dear Friends,

I have a requirement.  Below you can see a drop down which possess list of ref values ( Courier Company Names ) from Table Courier (Key column - Courier Name ).

jaichith_0-1718286812983.png

 

We will enter the shipment details such as Parcel weight, dimensions, delivery location etc. in Other fields in the form. According to that each courier company has its own tariff. For understanding purpose  and finding the cheap tariff . Is it possible to bring the dropdown with the price details as expected below. This help us to book parcels based on lowest price. 

jaichith_1-1718287232606.png

Note : Prices varies according the predefined tariffs in the database and distance, Air or Surface  etc. For all these will be calculated by using expression separately. 

Reference for Input fields :

jaichith_2-1718287525639.pngjaichith_3-1718287545390.png

 

 

 Thank you. 

 

0 13 290
13 REPLIES 13

The name is coming from the label column. If you add a virtual column that combines the name and the price, it will show it as you requested.

Should I add a virtual column in the parent table ? 

Parent table has the predefined values like cost, tariffs etc for each courier company. 

Predefined values are pulled from the parent table called COURIER it is used for the calculation purposes. 

My requirement is based on the calculation ( Weight x Cost ) done in the child table. Calculations are not done in the parent table courier. Then how virtual column as label helps here ? 

  

jaichith_0-1718296692012.png

 

 

The label column is the only way how you can affect what the dropdown shows with the Ref or Enum/Ref columns. Then the virtual column needs to be in Courier table. The other thing is how to write it in the most effective way and it doesn't affect app and its performance.

I think I cannot perform calculation in courier table.

One easier option I would try is to have the Courier Name reference column in the child table form as an Enum with base type as reference and the column to have input mode as button. Then after selecting all the parameters such as weight distance, mode, the user can easily compare the charges for different couriers by selecting the respective courier name.

Also I would try to order the courier names in the order of their per distance charges. So the cheapest appears at the start and costliest at the end in [Courier Name] selection. This may not work 100% because some couriers may charge for different parameters such as distance , mode and weight. But one can definitely create a ball park order for the user to select from cheapest to costliest. 

Also the courier name list can be possibly filtered in / out based on selection of weight, mode and distance etc. For  example, some may not be offering air or longer distances and so on. 

Of course , one can think of still more ingenious ways as well. 

Courier_Charges_3.gif

another expectation: 

A button can be included near the Courier Name Field in the form . that button takes to the new view where the parcel information size, weight etc are displayed and final calculated prices of each courier companies are display in the buttons / box. If the buttons pressed then particular courier name is entered in the courier name in the form . I could express my needs and expectations but I don't know how to do it. 

jaichith_1-1718358572128.png

 

@MultiTech , do you have any suggestions bro ?


@Suvrutt_Gurjar wrote:

Also I would try to order the courier names in the order of their per distance charges. So the cheapest appears at the start and costliest at the end in [Courier Name] selection. This may not work 100% because some couriers may charge for different parameters such as distance , mode and weight. But one can definitely create a ball park order for the user to select from cheapest to costliest. 


 

How to arrange courier names according their cost ? 

------------------------------------------------------------------------------------

My Expectation: 

jaichith_0-1718356840429.gif

If I have 10 to 20 + courier companies. button method may look congested. So I prefer dropdown and you have give the new idea of sorting based on charges. Kindly share the logic for it. I try to implement here. 

Note : I wish put the final calculated charges or amount in dropdown or buttons not the cost/kg.

I don't how to put the child table row calculations in the dropdown.  Please help @Suvrutt_Gurjar 

 

 

I am afraid you will need to develop based on suggestions as these are broad suggestions.

For example , in the test app I made to share suggestion for your this post's requirement, the Courier charges are as follows

Suvrutt_Gurjar_0-1718359753465.png

So the [Courier_Name] ref column in the child table will have a valid if of 

IFS([Mode]="Surface",

ORDERBY(Courier_Company[ID], [Rate_Surface], FALSE),

[Mode]="Air"

ORDERBY(Courier_Company[ID], [Rate_Air], FALSE)

)

This will sort the Courier_Name buttons with respective rates in ascending order. Of course this sorting is by input rate cost. Generally overall price will be proportional to the rate, is it not? If other parameters are same?

Also as mentioned, all companies may not be providing all services. Some may not be providing "Air" service/ So if "Surface" mode is selected, you can remove those companies from the available reference values.

In that case your valid_if expression could be

IFS([Mode]="Surface",

ORDERBY(SELECT(Courier_Company[ID], ISNOTBLANK([Rate_Surface])), [Rate_Surface], FALSE),

[Mode]="Air",

ORDERBY(SELECT(Courier_Company[ID], ISNOTBLANK([Rate_Air])), [Rate_Air], FALSE)

)

 

You can implement the above with a dropdown also but there will be additional clicks of the mouse to select a value from dropdown. 

You will of course need a little plug( or configure 🙂) and play to arrive at the best possible option.

 

 

 

Really valuable suggestions,

we will try to implement this. I have doubt in ref column types, valid if expressions are allowed ? I have not tested yet.  Learnt something new 🙂 today . 

If there is any idea of getting final calculated rates  either in dropdown or in separate view could help me better. 


@jaichith wrote:

another expectation: 


 

Well, these or almost similar approaches  could be possibly achieved, but in general these are bigger design aspects and may involve substantial configuration and generally beyond community suggestion zone.


@Suvrutt_Gurjar wrote:

Well, these or almost similar approaches  could be possibly achieved, but in general these are bigger design aspects and may involve substantial configuration and generally beyond community suggestion zone.


 

 

Dear @Suvrutt_Gurjar sir, 

Please give me your valuable suggestions regarding configuration and tricks to achieve my goals. 


@jaichith wrote:

I have doubt in ref column types, valid if expressions are allowed ?


You can search the community for many such questions. 🙂

Anyway, here are three relevant posts with accepted solutions. 🙂

Solved: Can I SORT the reference values in an ENUM column ... - Google Cloud Community

Solved: Sorting a reference column in a form view by the m... - Google Cloud Community

Solved: alphabeticly sort items in REF-Dropdown - Google Cloud Community

 

 

Top Labels in this Space