Jump to content

Two Fields Calculating off of each other


This topic is 5680 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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:

Link to comment
Share on other sites

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).

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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. :crazy2:

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:

Link to comment
Share on other sites

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:

Link to comment
Share on other sites

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 by Guest
Added update
Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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:

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This topic is 5680 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.