Difference between revisions of "Reference Table with Dynamic Validation"
From ADempiere
This Wiki is read-only for reference purposes to avoid broken links.
m |
(→Final Effect) |
||
(14 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. | + | *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. | ||
+ | |||
+ | <br> | ||
+ | ==Booking Window Result== | ||
+ | [[Image: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. | ||
+ | <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 27: | 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: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
- 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
- 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
- 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
- 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
- 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
- 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
- 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"));