I'm trying to link up two tables with ids without the user ever seeing an id. I have two tables:

PRODUCT

product_id - number - Auto-enter Serial, Unique

product_name - text

vender_id - number

vender_name - calculation - Unstored, =contact::contact_name

CONTACT

contact_id - number - Auto-enter Serial, Unique

contact_name - text

I have a relationship defined as follows:

vender_id = contact_id

The layout for the product has the name and the vender name (vender_id). The vender name is a dropdown list that uses a value list showing two different fields (contact_id and contact_name from contact table). The vender field will show the list of names when you choose them, but shows the id otherwise. I've tried to use an unstored lookup and then define the same value list for that field to choose from. This makes me feel really stupid for not being able to figure this out.

I know I could probably design the database where you click on the vender name and it takes you to another form with a list of names and then your choice modifies the vender id field, but I really just want to be able to use a dropdown list.

simple_relationships.fp7.zip

Welcome to FM Forums!!

Dropdown can't display the name when the ID is inserted. You must overlay with the Contact Name (attached). There is a merge field (based upon the Contacts::ContactName) on top of the VendorID dropdown field. Popups will display the name but hold the ID. FM should allow the same for dropdown but alas ...

If you don't want Users to see the number at all, change the text in the dropdown field to white (as I have done in this example). It will disappear when the field is popped. But it will still always display as black in the value list because that is the nature of dropdowns and popups fields.

Also, realize that you don't need an unstored calculation in Products - as I've indicated, you can simply place the related field directly on the Products layout. That is the power of relational ... cross placing fields rocks and eliminates the need of duplicate information!

UPDATE: Personally, I prefer using a popup when inserting an ID because it removes the possibility of typing in junk into the ID field, ie, it either contains a valid ID or it is blank. So if you use dropdown, I suggest you also validate it. Hmmm, maybe that is why FM doesn't allow it ... it is poor practice when one thinks about it. A dropdown allows alternate text...

LaRetta

simple_relationshipsMOD.zip

Edited June 23, 200718 yr by Guest
Added Update

I appreciate the quick reply, LaRetta. Your solution is something similar to what I've tried, but not exactly like that. It is a little bit of a hack, but knowing that is how other people would solve it makes me feel better about using it. You're absolutely right about the unstored field. I deleted it. It was just something I had seen used in FM's built-in "Lending Library". Perhaps I could also just use the field as a button that takes you to another layout to choose the contact (based on certain criteria) and then when a person clicks on a button next to the name, the vender_id field from the products table is populated with the corresponding id. Anyway, your solution is something I'll probably use somewhere as it works pretty much how I need it to. I've changed the validation for the field to make sure that the id actually exists before allowing it to be recorded.

-Matthew

It's a bit of a hack

Lol!! If you want to do anything decent in FM it's always going to involve a bit of hacking ;)

Yeah.....hopefully FM will fix some of its annoying quirks in v9. Overall, I still prefer FM to MS Access even though the latter is much more mature.

Perhaps I could also just use the field as a button that takes you to another layout to choose the contact

Eventually it comes down to that. Because, for example, if you have two or more contacts with the same name, there's no other way to enable user to pick the correct one.

Hi Matthew, and Welcome to the Forum.

Yeah.....hopefully FM will fix some of its annoying quirks in v9. Overall, I still prefer FM to MS Access even though the latter is much more mature.

I have never used Access, and would have to classify myself as a beginner with it. I have used several other databases however, creating my first database files with Database Manager 2 in 1983. I started using FileMaker in 1989 when I bought my first Macintosh. I have tried other Database Applications over the years, such as Double Helix, and 4D, but have not found any that I like better than FileMaker. Once you get past the learning curve, I think you will find it easy to develop solutions in, and use.

I can tell you one thing though. As a beginner with Access, I would not go on a Access Forum (if there is one), and start calling the things that are different from what I'm use to quirks, and the way to accomplish things hacks.

Again, welcome to the list, I think you will find the help you need here to overcome the differences.

Lee

I was a PC user since the days of Windows 3.1 and DOS and I recently switched to Mac in 2005. That is how I first got a chance to play around with a demo of FM. I didn't really spend that much time with it because I was too busy getting acquainted with OSX. About six months ago a potential job prospect considered knowledge of FM a plus so I downloaded the trial version (of 8.5) and a couple weeks after that, I purchased FileMaker 8.5 Advanced because I was so delighted with the features - particularly the ability to make cross-platform runtime solutions. Since that time, I've used FM to make a few databases that I use on a regular basis. I don't picture myself ever going back to Access.

No offense was meant with the terms "hacks" and "quirks". As a graphic designer, practically every bit of software I've encountered had something that could be described as quirks and I would have to research workarounds or hacks - particulary with web design using css.

Hey, I use access sometimes..

I'll tell you what, you really do have to hack that thing to make it do what you want some times. Plus frankly it just annoys me.

I am trying to achieve a similar thing with a Pop-up. I am using the Expense Report starter template.

1. I want to show the LastName,FirstName in a pop- up menu

2. I want to use a field global_search_Employee to store the EmployeeId of the selected item in the pop-up menu

I have a script which will use the value from this global field to perform search.

Please tell me how I should go about doing this.