Lookup issue - how to do it?

[bonzie]

I'm having in issue, where I need to be able to enter a value into a field (JobIdent); eg: "20080426", and I need to lookup via the Job table the actual ID (primary key) (JobID) and set that value in the field.

I'm having the same issue with the Employee and Code input, but once the correct method is found they can be fixed aswell.

All the tables use a sequence for a primary key, and that's how they are all related, but each table also has a 2nd (more human friendly) identifier that the user understands, and I need to convert from that to the database id in order to store records.

I've attached a screenshot that I hope helps. You can see there are 8 jobs in the system (shown in select box), if you select one of those, the 2 fields to the left are automatically populated by filemaker. But the client doesn't want to use a pulldown/select box, they want to instead enter "20080426" into a textbox, and then tab to the next input (less mouse usage). The system would obviously lookup that entered value to ensure it exists, and then set the field value to the actual Primary key (the serial)

Thanks for any input.

[bcooney]

Customers often cling to meaningful ID numbers. Looking up a unique ID from entry of a possible non-unique ID does not guarantee that you will indeed have no problems. What if they assign their meaningful ID to two records. FM will lookup the first it finds. (You probably won't know that there are more than one).

So, I vote for addressing the interface issue that is driving their need for meaningful ids. Can you give them portal selections, popups, new records from a parent? finds that result in a list view, from which they select a record?

[bonzie]

With the correct schema this is not possible, you set the field to unique and the db enforces that it remains unique.

Meaningful is important to users, say we were talking about instead of a larger entity. eg. Countries; filemaker allows you to use the country name (text field, as the primary key and link it to other entities). But this break standard data normalization rules. Relationships in most major db's I've used run much faster when they are int based Fk's.

The problem is the users, the current application they are using was written on DOS 3.xx, it's text based, no mouse, and designed for lots of very fast key/tabing entry. I've seen them input 100's of records in a matter of 2-3 minutes.

The current interface uses lists/select boxes and works perfectly, but the end result again is it takes them much longer to complete the same amount of work ;(

[David Jondreau]

Add a second TO of the Jobs table. Create a "Job ID entry" field and relate that to the user-sensible Job ID field. Have your REAL key field set to Lookup the real Job ID key based on that relationship.

Users enter their key into their field and the real key is populated.

Edited August 3, 2008 by Guest

[bcooney]

maybe you should read this thread, Link