layout problem on pop-up list by ID(pkey) relation

[Misho]

Hi All,

I'm new to filemaker and I've started with downloading some templates to see how the database works. I have a problem which I'm describing shortly:

There are 2 tables A with fields ::id (numeric;unique) and :descr and table B with fields ::id (numeric;unique) and ::refAid (numeric) which is related to A with relation B::refAid = A::id.

The layout in B is using pop-up list for field ::refAid to pick values from A. It could sound silly but I could not make the pop-up list to show the A::descr field (only) and to save A::id field in B::refAid. I try to hide all of the IDs from the layout (the users should not see them ) and to make all of the relations by numeric fields (primary keys).

Am I missing something really simple to make it working like this.

Thank you for your help

Misho

[-Queue-]

You have to make B::refAid match A::id either manually by setting refAid via script or by using a portal in table A with the relationship to B set to 'Allow creation of related records'. Simply creating a relationship between them isn't enough; the data in the fields must match also, or the relationship is not valid.

[Misho]

I want to choose the responding value from A in the B layout (so I think I don't need portals), I'm attaching a quick made test_db.fp7 template file that I've managed to do the thing but I see the a:id. It will be great if you have some sample files that I can see how a script is working in this case.

I have experience with web client-server-database development and there every SELECT box has "value" that goes to the database and "description" that is showed to the user. Probably I'll have to start thinking in fileMaker manner

Misho

[-Queue-]

Okay, I see what you are doing. The relationship doesn't even come into play at this point. What you have set up is the way it's usually done. You can't hide the id as it is required for entry into the refAid field.

[Misho]

That's why all of the templates I've downloaded (even ones written by programmers www.murrayc.com - which I found very useful) make it in roundabout way using "code" and relation by code ( which is text and is not supposed to work better than number ).

OK in my case is it possible to make descr field ( in this example is regular table field but it will be calculationa value in general ) which I'll be choosing from and after this to make a script which will set up my refAid field depending on the chosen value. I've found that the FM script language is too resticted though, but hope it will be good enough to do the job.

I wonder how do everybody make simple relation between two tables like this are you always showing the IDs or "codes" ( I'd appreciate any example or link )?

Thank You so much for the fast replies

Misho

[-Queue-]

You can add an additional text field refAdescr and create a relationship from this field to descr in A. Make refAid an auto-enter calculation of descrrel::id with 'Do not replace existing value' deselected. Now whenever refAdescr is changed, refAid will update with the related id.

Stack these two fields (unless you don't want to see the refAid) with refAid on top, formatted with a white background. Disallow entry to refAid and format refAdescr as the list.

See modified db attached.

Test_db.zip