Autopopulate multiple fields based on numerical entry/lookup

[Lorne]

Hi,

I've just started working with Xataface a few months ago and I'm impressed, but I'm running into a challenge trying to figure out if I can do what I'd like to do. Here's an example of what I'm trying to accomplish.

I've got one table, call it stafflist. It has fields such as:

ID Number
First Name
Last name
Phone Number
Address

I've got another table called Schedule, it has

Event ID
Date
Start time
End time
Staff ID
First Name
Last Name
Phone Number

I'd like the Staff ID field in the Schedule table to be a numerical field that can be typed. If someone enters an ID that exists in the stafflist table's ID field and hits tab (or clicks on an icon beside that field) I'd like it to auto-populate the remaining 3 fields with the first name, last name, and phone number, pulling the info from the stafflist table. If that ID does not exist in the stafflist table (or is left blank) then the person entering data into the Schedule table can type out the rest of the information.

I know this type of relationship can be done with pull-down menus and such, but a pull-down menu does not offer the option of typing an answer that does not exist in the pull down menu. Also, if I have a huge list of people in the stafflist table (let's say there are 500 individual numbers / names in there for example) I would not want to use a pull-down to show that - I'd rather just manually have the ability to type in the number, hit tab or click an icon, and have the rest auto-populate and display on the screen before the record gets saved.

Is this possible?

Thanks in advance,

Lorne

[auphi]

Lorne,

Have you used the lookup widget yet? When you click on the field it will give you a list, it's searchable, and there'll be a button to "add new". It's not *exactly* what you were asking for, although, I tend to think it's actually a more elegant solution, both in terms of GUI and there's less of a change of "extras" being added in due to a quick typo/misspelling... *and* it will save you having to mess with a bunch of javascript. The lookup widget also has some nice filtering capabilities, which I often find quite useful, and you can use a concat() in the sql in your valuelist to just group things together so that you only need 1 field (let's call it "Staff"), and all items will be searchable from the same lookup.

Here's an example, (although, fyi I just freehanded the code and didn't actually check for errors, so just in case something doesn't work, that might be why):

fields.ini (for Schedule table)

Code: Select all

[staff]
widget:type = lookup
vocabulary = teacher_list


valuelist.ini

Code: Select all

[teacher_list]
__sql__ = "SELECT staff_id, CONCAT(staff_id, ' - ' ,first_name, ' ', last_name, ' - ', phone_number) as staff_info FROM stafflist"


In your "staff" field, all this will save is the "staff_id", which is all you typically need data wise (assuming each is unique), but your users will see "[ID#] - [First_Name] [Last_Name] - [Phone_Number]", which is a nice human readable format.

[Lorne]

Hi,

Thanks for pointing out the lookup widget. I tried it out and you are correct - it is close to what I'm looking for, but not quite.

The difficulty that I had with the lookup widget is that it seems that when I use it, it will always pop up the lookup window when the field is selected or clicked into. This is not ideal is that I'd like to have the option of a user being able to just click in the field and type in a number if they know the number without having to go through the lookup process. If they don't know the number then there should be an option to click on the search / lookup and search for it.

The other thing I ran into with the example - while using concat does have the ability to show the 2 fields, it won't search on both fields (the way the lookup works, it only searches on the field being looked up - so if it is a text field you can only search for the text stuff). I'm sure there is some way to work around this, I just havn't done enough experimenting yet.

This widget may still work for me, it does look promising. Thanks for the info.

Lorne