Autopopulate multiple fields based on numerical entry/lookup

A place for users and developers of the Xataface to discuss and receive support.

Autopopulate multiple fields based on numerical entry/lookup

Postby Lorne » Fri Feb 15, 2013 11:55 pm

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
Lorne
 
Posts: 2
Joined: Fri Feb 15, 2013 11:31 pm

Re: Autopopulate multiple fields based on numerical entry/lo

Postby auphi » Sun Feb 17, 2013 12:27 pm

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.
auphi
 
Posts: 20
Joined: Sun Oct 21, 2012 7:39 pm

Re: Autopopulate multiple fields based on numerical entry/lo

Postby Lorne » Wed Feb 27, 2013 11:00 pm

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
Lorne
 
Posts: 2
Joined: Fri Feb 15, 2013 11:31 pm


Return to Xataface Users

Who is online

Users browsing this forum: Bing [Bot] and 4 guests

cron
Powered by Dataface
© 2005-2007 Steve Hannah All rights reserved