How to Implement Advanced Lookup on Field, i.e. Based on Separate Grid Application

Scriptcase 8 Discussion
#1

ScriptCase has already implemented a ready made lookup function on fields defined as type “select”. This gives a simple selection list of a text field values from a related table, but inserts the key (e.g. an integer ID) from the related table to the field from the first master table that needs validation. However, sometimes a more advanced lookup may be needed, for example a node_id from a related table that may contain hierarchical structure with several fields in it’s own right.

In such cases a simple drop down selection list is not really adequate. Rather, a full grid application with search features would be in order. One can create nested grids, but they are not bound to specific fields in the master table (AFAIK) and they are shown either below the original master table or in an extra, own field. What is needed is that the lookup grid application is run in modal mode and that a specific key or node-id can be returned and inserted in the relevant field in the master table.

Any ideas on how one could implement this is appreciated.

#2

Hi There,

I hope i have read this correctly, what i do is create a mysql view, then create a new grid with this sql use the sql in the grid creation not after grid has been generated., it will then create all the fields for you. i hope this helps and i understood the question.

#3

In scriptcase you inform two fields in the select command used in lookup of fields, the first is the key and the second is the field that you want to show in application, e.g. SELECT id, name… the id field will be included in database and the name field is showed in application.

You must inform the two fields, you can use same field to key and show. Like this example below:

SELECT ROLE_DEPARTMENT,ROLE_DEPARTMENT
FROM dept_role
ORDER BY ID

Hope this is what you are looking for.

Tim

#4

Thanks for your feedback Scotty and Tim.
I know that you can relate from one field in a master table to a lookup table as described by Tim. However, it is something more advanced that I am looking for.

In Tim’s example you have a lookup in dept_role table to find the id of the dept_role record to be used in the master table (for example named employee). If you have a simple text field with the name of the department role (e.g. dept_role.name), it can then be used as a dropdown select in the employee master table for the employee.dept_role_id field.

However, what if department roles were not that simple. For my example, let’s assume that there were thousands of department roles, they were organised hierarchically and there were additional fields needed to be searched on in order to find the right department role for an employee – a rather contrived example, but it illustrates my problem well. In this case just defining the employee.dept_role_id as a select field where the select drop down field in the master employee form displays the names of the department roles (i.e. dept_role.name) would not suffice. One would need a separate, lookup, grid application in it’s own right with search facilities on several fields of the dept_role table.

Creating a grid application for department role is easy in scriptcase, but how can one link the grid application to the master form, so that it is bound in modal mode to the employee.dept_role_id field in the master form, for example by having a button that opens the grid application for department role in modal form and returns the selected dept_role_id to the employee.dept_role_id field in the master form.

#5

I am a ScripCase newbie … below may be possible/applicable use cases to the decribed scenario:

Regards, Deon
#6

Thanks for the references, Deon.
Unfortunately, I do not see how they can solve the problem. The Editing-select-field gives a possibility of editing or adding a record in the related table (category in the example) from the master form (products). There is no search facility beyond the alphabetically listed category name in the drop down field of the master form (product table). However, In my use case, I would have thousands of existing categories and would need a grid/search application to find the right one.

I am thinking that perhaps one might have a user defined button in the master form application that can call the grid application with the ID of the master record being edited as a parameter (i.e. received as a global variable). The grid application must have a row based button implemented to select the related record and call the original form application with both the ID of the original master record as well as the ID of the record in the related table that was selected in the grid application. The master form application will then be reloaded and can perhaps be brought to show the original record and fill the selected ID from the related table into the appropriate field in the master table. It may just work, but will be rather cumbersome. A natural, built-in solution from Scriptcase could be much more elegant.

I will have to look more into when I return from holiday, but if anyone has tried to implement something similar or have other ideas, I would be happy to hear about it.