Relationship fields needed to filter portal records

[Dataslinger]

OK - in short, we have a database to track grants. So there's contact table, a grants table, and a milestones table. We have relationships that tie the contacts to the grants and then relationships that connect the grants to the milestones.

Under the main - Contacts layout, I want to have two tabs, - one tab portal that lists all the "pending" milestones per this contact, and one tab portal that lists all the "completed" milestones.

I'm trying to figure what fields (calc) and what relationships I need to set up to do this. So I want a relationship between Contacts and Milestones that filters/separates by whether the Milestones are completed or pending. I'm having trouble connecting Contacts to Milestones.

[Fenton]

You're right, there is no field in Contacts for the type of Milestone. But you need one for the filter. So what you can do is create what I think of as a text constant calculation. You'll need 2 in this case.

z_cMilestonePending_txt = "pending" // just the text "pending" in quotes

z_cMilestoneCompleted_txt = "completed"

I add the suffix "_txt" to the name so I know what these are. Set the Storage to [x] Do not store; so they don't bloat your file.

Then you can create a relationship to Milestones:

Contact ID = Milestones::Contact ID

AND

z_cMilestonePending_txt = Milestones::Status (or whatever the field is called; you didn't say)

Another relationship for completed.

[Dataslinger]

Fenton, this is so clear. Thanks for the time to post.

Now, one additional issue I'm having with the keys... in creating a new Grant related to a Contact, I copy then paste the ContactID key number into the new grant record via a script. Then when I create a new Milestone for the Grant record, I copy the GrantID key into the new Milestone record also via a script. What's the best way to carry forward the ContactID key into the Milestone record? Seems one can only do one copy-paste in script... and I've tried looked up and calculated to either bring this forward from Contacts or Grants, but no joy so far. Thanks again.

[Fenton]

It's almost never necessary to use copy/paste to pass values (an exception would be the Copy All Records step). Copy/Paste requires the field on the layout, creating a (unnecessary) possibility of failure.

Put each value into its own global field, while you're in the context where the value is local. You can then access that global field from anywhere else, via any relationship to its file/table.* A global value is the same for all records in a its table, accessible from anywhere else.

Because of this capability, it doesn't much matter which file/table the global is in (you can have a file/table of only globals if you want). But one usually creates them in the regular value's primary table.

In modern (-) versions of FileMaker you can use a script Variable for this. But it's much the same.

*Or no relationship in FileMaker 7/8, just a table occurrence.

[Dataslinger]

Fenton, thanks for the quick response. I'm not quite following you on this. Say the Contacts table has a _kp_ContactID field which is a Number field, it is Indexed, and it's Auto-Enter - in that it's a serial number, incremented by 1. So this is the primary key. I don't see how to make this a Global field which I can then "parse" over into Grants (as they're created) into the _kf_GrantsContactsID field and then into "Milestones" per Grant as they're created. I'm sure it's a simple process, but confess to not understanding 'the how' of what your suggesting. Thanks again!

[Fenton]

No, don't make the current ID field a global field (yikes!). Create a separate global field, _g_ContactID. Then:

Set Field [ _g_ContactID, _kp_ContactID ]

Now the value that was in _kp_ContactID is in _g_ContactID, and is available from Milestones.

[Dataslinger]

Fenton, it must be Friday and I'm just not getting something. OK - create _g_ContactID (global) in Contacts - then is it "Set Field [_g_ContactID, _kpContactID" as a script in Contacts file as well?

Each time I run the script, depending on which record I'm on, it updates the auto-entered, serial numbered ContactID.

Are the field and script in the right place?

Now when I try to bring the Global field into Milestones... I don't see it. Thanks for the input.

[Fenton]

"...in creating a new Grant related to a Contact, I copy then paste the ContactID key number into the new grant record via a script."

I was assuming you are in the Contacts file when you copy the ContactID. I think you need to read some more about global fields. They are critical to using FileMaker. If there's a value in a global field (put it on the layout to see), but you can't see it via a relationship, then it ain't a global [commonest mistake on earth -]

I don't see how setting a global field could possibly affect the serial Id field. That would only happen if you created a New Contacts record; how could that be happening?

If you could post an example of your file(s), even a clone, AND tell use where you're starting from and where/what you want to accomplish, we could likely see where it's going wrong. These things always depend on the context of where you are when you run it, which we can't see from here.

[Dataslinger]

Fenton, thanks for the help. I think I've gotten the global field working, to bring the ID key into other tables. And... yes, I will try more to 'wrap my mind around Global field usage' - good point! Thanks

[Dataslinger]

Hi Fenton, I was wondering if you could lend more assistance on this one. We've gotten the Global field of the ContactID to appear in the Milestones data file... though at times it seems to not track correctly. So now, though I can do this so easily in FMP 8.x's relationships, I'm trying to get a combination ContactID key AND Milestone's status (completed or pending)... to setup a relationship so I can use a filtered portal. The failure seems to be that a calculation of _g_ContactID & MilestoneStatus, will not give me the ID number as it's a global and cannot be indexed. The target FileMaker for this, is from my profile... FMP6. So I need way to make happen in FMP6, though I can do already in FMP8.x. Yes I know we should upgrade to FMP 8.x but that's not going to happen now. Thanks for further guidance on this.

[Søren Dyhr]

With fm6 should you take a look at this:

http://www.filemakermagazine.com/videos/data-tagging-classification-vs-organization.html

--sd