Two Fields Calculating off of each other

[David Garza]

Hello,

I have my DB laid out as such:

Interview::cID

Interview::Contact Name

I have cID setup to lookup the ID # for Interview::Contact Name, and Interview::Contact Name to lookup the Full Name from the cID #.

The problem I come across is one calculates and the other can't for some reason. What can I do to resolve this?

Thanks and God Bless!

David

[Genx]

What sort of fields are these first of all, and what sort of storage (i.e. unstored or stored?)

~Genx

[IdealData]

The problem I come across is one calculates and the other can't for some reason.David

Did not FM report some errors or problems when you created the field?

[LaRetta]

Hi David,

I have cID setup to lookup the ID # for Interview::Contact Name, and Interview::Contact Name to lookup the Full Name from the cID #.

Lookups fire AFTER they have a relationship. What field is the relationship based upon? Is it the ID? An ID shouldn't be a calculation in this example; but rather, when you choose the ID in one table/file, it then establishes the relationship and all lookups based upon OTHER fields (within that same table/file) will fire and look up related information from the other.

LaRetta :wink2:

[xochi]

If these are not lookups (but auto-enter calcs), filemaker 8v3 supposedly fixes some bugs with how multiple auto-enter-calc fields compute. In 8v1 the results could depend on the order you created the fields in. In 8v3 this is fixed (so they say).

[David Garza]

Hello,

Here is what I am referring to. Please let me know the best way to explain something like this so I know for future. I have attached a demo FP7 file.

I have auto-enter calcs for Customer ID and Contact Name (each lookup is dependant on the other field).

Thanks and God Bless!

Example.zip

[LaRetta]

Hi David,

I didn't explain myself very well and your post was nicely stated. You have a 'chicken-before-egg' syndome here and it isn't possible. One can't look up (or auto-enter) until a relationship is established. And a relationship can't be established until the lookup or auto-enter happens (with your example). I may be wrong but I think this classifies as a circular join (and FM is right not to allow it). This is what Mark was asking you, ie, what error message did you get when you attempted it?

An action must happen to create a relationship and only then can the remaining fields pull from that relationship. In other words, in Data you can select Contact Name. THAT relationship could then pull the ID from Contacts - or the other way around ... but not both.

Use ID = ContactID (only) as your relationship. Then Auto-enter the ContactName (if you must). Having the ContactName in both tables is sometimes considered redundant data but sometimes it is required for history purposes.

LaRetta :wink2:

[comment]

I don't see a circular reference in the demo - the two "lookups" are from two different TO's. And it seems to work fine in 7.0v3 and in 8.0v1, so I suspect xochi is correct about it being a version issue.

[LaRetta]

It works fine in 8.0v2 as well. I'm obviously confused on what is wanted because the demo doesn't match the first post, ie, the ID and Name both being looked up from each other. I don't care if they are different TOs, you couldn't look up an ID based upon the Name TO and then look up the Name based upon the ID TO. I thought 'what was breaking' was a lookup (or auto-enter) not placed in this test file yet (attached to the ID field). Even the thread title indicated that.

Apologies if I confused the thread. :wink2:

[comment]

I confess I didn't understand the first post. But you CAN look up an ID based on a name, then look up the name based on the ID - though it seems to depend on field creation order.

Wheteher this is a wise thing to do is another matter.

CircularLookup.fp7.zip

[LaRetta]

And we've been here before. It works - I can change names UNTIL I enter the SourceID field definition (it breaks if I click OPTIONS then back out). Then I can't change the name any more.

I'm dragging my feet in upgrading to 8.0v3 this time. But I'm dying to know how this is handled in the upgrade.

UPDATE: David's file continues to work for me even if I enter the Options and calculation.

Edited May 11, 2006 by Guest
Added update

[mdpres]

I just tried this with 8.0v3 and can only get it to work with the source id. In fmpro 8.0v2 it dows work ok. I have an idea if it pans out i will post it.

[David Garza]

Hello,

I've tried my example again and comment's example, and I found neither work. Comments did at first then stopped for some reason.

I can lookup the Name based on the ID, but not the ID based on the name.

Thanks and God Bless!

[LaRetta]

I can lookup the Name based on the ID, but not the ID based on the name.

I think we had better get clear on exactly what you are attempting to do. Because your file is not structured like Michael's. You keep saying you want to lookup the ID and Name. But your file's graph names don't match your original post. Neither do the field names. I only see two auto-enters (ID and email) ... I see no lookup or auto-enter on ID and Name. Please clarify ...

1) Do you want an auto-enter or lookup on the ID# in your Contacts file and FM won't let you?

2) Is the email or ID in Data not looking up correctly?

3) Is it something else entirely?

Comments did at first then stopped for some reason.

Once we pin this down, we can tell if we have version-behavior differences or whether we can help you in other ways. Michael's file works in 7.0v3 and 8.0v1. It breaks once I enter Field Definitions in 8.0v2. And it doesn't work for Dean with 8.0v3. But your file works fine for me (I think), although I see no name lookup OR auto-enter. What did you think about our discussion on versions? Do you think the reason Comment's stopped for you might be your version? When did Michael's file break for you? What version are you using, David? You haven't told us that yet. In fact, you're weak on the information and we can't help you this way, so cough it up. :wink2:

[comment]

In case it wasn't clear: the file I posted above is for amusement only. I would NOT recommend to actually implement this technique.

The correct method is to select the ID directly from a value list that also shows the names. In version 8, you can choose to hide the ID's in the value list definition.

[David Garza]

Hello,

To answer your question the best I know how:

I am attempting to perform two lookups at different times.

lookup 1: When the Contact Name changes, it will lookup the Contact's ID # and display it on the page.

lookup 2: When the ID # changes (if I enter a contact ID # manually), I would like it to lookup the NEW contact name based on the NEW ID # I had entered.

The way it seems to be happening is, when I change the Contact Name, it attemps to change the Contact ID (From a lookup), however the Contact ID then does a lookup on the OLD name and reverts the Contact Name back to the old contact name. THe same thing happens with the Contact ID #.

I guess i don't know how to illustrate this in FileMaker.

Thank You for all of your help!

David