Difference between revisions of "Reference Table with Dynamic Validation"

From ADempiere
Jump to: navigation, search
This Wiki is read-only for reference purposes to avoid broken links.
m (Steps)
(Final Effect)
 
(11 intermediate revisions by the same user not shown)
Line 1: Line 1:
 +
This tutorial is started by [[User:Red1|Red1]]. If you like to comment for improvement, please do so here at [[Talk:Reference Table with Dynamic Validation]].
 +
=Motivation=
 +
*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=
 
=Case Study=
 
*We are doing this as a prototype for an [[E-ticketing]] system.
 
*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.
 
*We are going to create a '''Reference''' to a '''Table''' in the database.
*The ''Reference'' are created for the field '''ET_TripBegin''' and ''ET_TripEnd'' from table '''ET_Booking'''. They will access the ''Table'' called '''ET_Vehicle'''
+
*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=
 
=Steps=
 +
==Table and Column Setting for Vehicle==
  
 
[[Image:ReferenceTable1.png]]
 
[[Image: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.
 
*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.
 
+
 +
<br>
 +
==Reference Table Validation Setting for TripBegin Field==
 
[[Image:ReferenceTable1Validation.png]]
 
[[Image:ReferenceTable1Validation.png]]
  
 
*The table is selected here and the field chosen is the ET_Departure which becomes distinct origins during pull down.
 
*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.  
 
*You can see the result from the following screen.  
 +
 +
<br>
 +
==Booking Window Result==
 
[[Image:PulldownReference.png]]
 
[[Image:PulldownReference.png]]
 +
 
*It shows all the available origins that the user can choose from.
 
*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.
 
*Now we shall do the same for the other field which concerns destinations or arrivals.
 +
<br>
 +
==Table and Column Setting For Booking==
 
[[Image:ReferenceTable2.png]]
 
[[Image:ReferenceTable2.png]]
  
 
*This second field ET_TripEnd will refer the same table. Note that it has an extra duty under Dynamic Validation.
 
*This second field ET_TripEnd will refer the same table. Note that it has an extra duty under Dynamic Validation.
  
 +
<br>
 +
==Reference Table Validation for TripEnd Field==
 
[[Image:ReferenceTable2Validation.png]]
 
[[Image: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.
 
*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.
  
 +
<br>
 +
==Dynamic Validation SQL Code==
 
[[Image:DynamicValidation.png]]
 
[[Image:DynamicValidation.png]]
  
Line 30: Line 54:
 
*Here we ask it to access the ET_Vehicle table for records similar to the origin by ensuring similar ET_Departure values.
 
*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.
 
*@..@ traps the field value in play. We place it within single quotes '@..@' for SQL to take it as a string value.
 +
 +
=Final Effect=
 +
[[Image: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 [[E-ticketing#The_Callout|Callout]] as the pull downs will clear during Save.
 +
 +
=Passing Values Via Rules=
 +
*You can create a Rule script and assign it to the Callout so that it copies over the values to other fields and refresh:
 +
<pre>
 +
A_Tab.setValue("booked_origin",A_Tab.getValue("Origin"));
 +
A_Tab.setValue("booked_destination",A_Tab.getValue("Destination"));
 +
</pre>
  
 
[[Category:HowTo]]
 
[[Category:HowTo]]
 
[[Category:Training Course]]
 
[[Category:Training Course]]

Latest revision as of 19:13, 28 February 2011

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

  • 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

  • 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

Table and Column Setting for Vehicle

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

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

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

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

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

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

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

  • 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"));