Reference Table with Dynamic Validation

From ADempiere ERP Wiki
Jump to navigationJump to search

This tutorial is started by Red1. If you like to comment for improvement, please do so here at Talk:Reference Table with Dynamic Validation.

Motivation[edit]

  • How would you like a field in your window that has a pull down list containing values that are dynamic?
    • which is dependent on another table in the database?
    • which will select distinct values or group them into unique occurrences if they are not unique?
    • easily achieved without coding but some mouse-clicks to setup?
  • How would you like a field that has its pull down list populated with values controlled by the value of another field?
    • with no source-code change?
    • easily done with simple proto SQL phrases?
  • You have come to the right place as we shall show you exactly how quick and easy that is done here!

Case Study[edit]

  • We are doing this as a prototype for an E-ticketing system.
  • We are going to create a Reference to a Table in the database.
  • The Reference are created for the field ET_TripBegin (Origins) and ET_TripEnd (Destinations) from table ET_Booking. They will access the Table called ET_Vehicle that is a holder of available routes or schedule trips.

Steps[edit]

Table and Column Setting for Vehicle[edit]

ReferenceTable1.png

  • The ET_TripBegin field Reference is set to Table instead of String. A new Reference 'Departures' is created. You can do that on the fly by right-clicking on it and select Zoom.


Reference Table Validation Setting for TripBegin Field[edit]

ReferenceTable1Validation.png

  • The table is selected here and the field chosen is the ET_Departure which becomes distinct origins during pull down.
  • You can see the result from the following screen.


Booking Window Result[edit]

PulldownReference.png

  • It shows all the available origins that the user can choose from.
  • Now we shall do the same for the other field which concerns destinations or arrivals.


Table and Column Setting For Booking[edit]

ReferenceTable2.png

  • This second field ET_TripEnd will refer the same table. Note that it has an extra duty under Dynamic Validation.


Reference Table Validation for TripEnd Field[edit]

ReferenceTable2Validation.png

  • The pull down will show all the arrivals. But we do not want all of them. We just want those that correspond to the origin.


Dynamic Validation SQL Code[edit]

DynamicValidation.png

  • Dynamic Validation allows for SQL injection, where we supply the WHERE clause.
  • Here we ask it to access the ET_Vehicle table for records similar to the origin by ensuring similar ET_Departure values.
  • @..@ traps the field value in play. We place it within single quotes '@..@' for SQL to take it as a string value.

Final Effect[edit]

Booking.jpg

  • We see that only destinations associated with the origin is displayed in the pull down. The selections are pushed to the upper row by a Callout as the pull downs will clear during Save.

Passing Values Via Rules[edit]

  • You can create a Rule script and assign it to the Callout so that it copies over the values to other fields and refresh:
A_Tab.setValue("booked_origin",A_Tab.getValue("Origin"));
A_Tab.setValue("booked_destination",A_Tab.getValue("Destination"));