Help creating a relationships & lookups

[mewesley]

Hi all,

Embarrassed to say I'm not exactly new to FM. I have been using it for 2 years and have cobbled together a couple of (flat)solutions. My (main) problem is that I still don't understand how to create a relationship. Maybe I don't understand the concept completely. I have bought books (and read them, too) but, still I'm confused. Now I really need to create some (relationships).

When I try, following the examples in books, I get an error message that reads...

"this relationship uses the name "blah-blah" in it's name choose another" or, when creating a lookup, ...

"this field requires a unique number" , which is not true on this layout, but is on the layout I'm looking up from. (id::id)

Can someone hold my hand and show me the differences and the correct method? Please.

Thanks,

Wesley

[-Queue-]

I've never seen an error like you describe concerning relationships. What exactly is the name that it does not like?

Regarding the other error, if a condition is true for a field on one layout in a file, then it holds true for every layout in that file with the given field on it. To put it another way, field definitions/validations are constant throughout a file. Layouts are simply a way of displaying these fields graphically; they have no effect on the fields themselves (with the possible exception of a field with 'Status(CurrentLayoutName)' or 'Status(CurrentLayoutNumber)' in its definition.

I would be glad to help if you would provide more detailed/specific information regarding your problems.

What version of FileMaker are you using? This will affect what you're able to accomplish with relationships.

[mewesley]

-Queue-,

Thanks for your reply. The field names are...

Settlement Layout

control# (unique, auto entered, increment by 1, indexed)

Dispatch Layout

control# (indexed, strict, numeric)

The exact message ( I err'd on the the previous )...

"This lookup uses the field "control#" in it's definition. Choose another field.

That was the error msg for a lookup not relationship, sorry.

What I'm trying to do is...

I have a layout (separate db) that I am trying to create a lookup to place info on another layout in another db.

I am on FMP 5.5, WinXP

Thanks,

Wesley

[-Queue-]

So, your Settlement Layout is in one db and Dispatch Layout is in another db, correct? And you're trying to create a match field for the Dispatch file to display related data from the Settlement file. Am I following so far?

It sounds like you received the error because you were trying to lookup the control# based on the control# relationship, which doesn't make much sense. You'd only want to use a lookup for other information in the related file based on the match field, not the match field itself. You only need set the field in the related file with the desired matching number (possibly using a script) to display all information for the related file.

[mewesley]

-Queue_,

Could you give me an example? I didn't know that I couldn't use the control# to lookup the control#. How can I do what i need to do?

<You only need set the field in the related file with the desired matching number (possibly using a script) to display all information for the related file.> confuses me.

You only need set the field in the related file = to me that means I "set" the control# field in the dispatch layout to the control# in the dispatch file.

That can't be correct, can it?

Wesley

[-Queue-]

Assuming Settlement and Dispatch are two separate files (let's try not to use 'layout' terminology as it can be confusing and layouts don't effect the fields or files), and Settlement has a unique control#, then Dispatch should have a field (it need not be unique only of the same field type, if one Settlement can have multiple Dispatches) that is set with the control# from Settlement when a new record is created (either via a portal in Settlement with 'Allow creation of related records' checked in its relationship or through a script)

Check the attached files for a simple sample that may help you understand the concept. Check the field definitions and the relationship definitions for both files.

example.zip

[mewesley]

-Queue-,

Thanks for your help, I really do appreciate it. Quite honestly this is going from bad to worse. As I look at what you said and what other advice I've been given along with what knowledge I have, I have to say-the whole db needs to be re-designed, re-thought, re-everything. I did it all wrong from the very begining, which I've known for at least 18 months. I've just been making clumsy workarounds to make it work for me. I can see, now more clearly than ever, how important a good design is to start with.

Even though I do need to learn and understand the concepts of relationships and lookups, among other things, making another workaround to my current problem doesn't alleviate the true problem- a bad design. I think, maybe, this is a part of my confusion. How can I learn, if I don't have the correct design to apply my learnings to? That's why it doesn't always work when I try to create a relationship, or doesn't give me the expected ( or hoped for) results. Because I'm trying to follow a procedure, without fully understanding it first, on a layout design where everything is worked-around?

Many thanks again,

Wesley

[djgogi]

Take a look on this URL it could help you to clarify some basic concepts.

Dj