Hi Everyone!

I'm not sure whether to post my problem here or in portals, because I'm not sure why I'm getting phantoms!

Main dB is Services. Services is related to:

Client on ClientID

Enrolments on ClientID

Payer on PayerID

Each Enrolment is also assigned a PayerID (multikey).

I have an Enrolments portal placed on a form in Services. It contains Enrolment::(various fields), Enrolment::PayerID and a Payer::cPayerName, which is a calc (text), stored.

But, even though the Client only has one Enrolment, it shows two cPayerName calcs! The rest of the portal line (all Enrolment information) is blank. I tried deleting a blank row and nada! Enrolments dB only lists one record for the Client. And, there is only one PayerID in the multikey in Enrolments. I've had this happen before and have never figured it out.

It appears that there is something important about relational-dB theory that I've missed Can anyone explain it?

LaRetta

I think that your relationship (Enrolment) has "allow creation of related records" checked.

Since you have put an field form non matching relationship (Payer) into portal, the values from first matching related record will be displayed in evry row of enrolment portal.

As I said, if your relationship is enabled for creation of related records than the "phantom" row, which serves for creation of related /enrolment) records thru portal will diplay values from FIRST related record from relationship Payer.

To avoid this you could:

1) Disallow creation of related record

or If the first option is not applicable

2) bring data from Payer to Enrolment and display this tunneled field in your Enrolment portal.

Dj

Hi DJ

Thanks for explaining it. So the reason a phantom appears is because I put a field from a 'non-matching relationship' (Payer) into the portal (non-matching meaning Payer is not directly related to Client) and FM, being the sweet program that it is, is attempting to give me a method to add a row.

Well, I need to be able to create records in that Enrolments from Services! And I hate the thought of creating an unncessary calc in Enrolments just to display the Payer Name

Hi LaRetta,

Not going in depth into your post and files, is it just impossible to create a t_payer name in Enrolment and set it as a Lookup from c_payername (you didn't mentionned a relation from enrolment to payer...).

Then just place the Enrolment::t_payername in your portal.

Edited :

I'm quite sure that is what DJ is suggesting

2) bring data from Payer to Enrolment and display this tunneled field in your Enrolment portal.

Am I missing something ?

Hi Ugo!

Well, I do have a relationship between Enrolment and Payer on PayerID. I may be mis-understanding something here (it happens a lot), but I don't want to add a field or a calc to many of my dBs just to grab a Payer Name. Why should I? I'm just not convinced that it's the best solution!

It seems better to use a script to add records to a portal, remove "allow creation of related' and continue to use the cPayerName from my Payer dB in all of my portals. Am I still missing an important point?

LaRetta

LaRetta,

Here is what you told us :

I have an Enrolments portal placed on a form in Services. It contains Enrolment::(various fields), Enrolment::PayerID and a Payer::cPayerName, which is a calc (text), stored.

and

I need to display Payer Name in MANY of my dBs

Here is what DJ replied

bring data from Payer to Enrolment and display this tunneled field in your Enrolment portal.

Here is what I replied

is it just impossible to create a t_payer name in Enrolment and set it as a Lookup from c_payername ?

Hmmm...

You don't need to have that payer name on all of your files. But if you really need it to fill the portal, then use a lookup from the relationship Enrolment::Payer...

If you do not need it, so where is the problem

Now choosing from scripting a new entry or "Allow creation of related records" via a Portal is mostly linked to the relational design of your solution. How far do you want these files to relate ?

Besides, I like to use lookup fields

From my last post:

Besides, I like to use lookup fields

I agree they may be a problem when you script a relookup (if you don't want your field to change), but in that particular case, the lookup is based on a calculation (which I can figure is "safe"), so there is no risk.

Hi Ugo

You don't need to have that payer name on all of your files. But if you really need it to fill the portal, then use a lookup from the relationship Enrolment::Payer...

I DO need Payer Name to *display* in many of my dBs and they're not all related to Enrolments. Payer relates to Services, Contracts, Enrolments, Invoices, Payments, GL, etc. That's why I just have one calc in Payer to display the name. This works fine in every situation except a portal. I just don't want to add lookup fields or calcs in all my other dBs (which contain a portal with PayerID) just to display for a portal which contains a PayerID. It appears to be overkill and will bloat my dBs. I think it'd be more resource-efficient to remove "allow creation of related" from my portals (which will then eliminate the phantom) and attach a script to the fields which will create new records.

Calcs can be either stored or unstored. If they are stored then you end up writing and maintaining an extra 20,000 fields of data (in a file that has 10,000 records). If the calcs are unstored, you load the processor with thousands of additional calcs during every user session. That's per CobaltSky. Oh, each calc should be seriously considered, in my opinion. That's why I only have one, and it's in the Payer dB. Adding a lookup field will also increase the size of each dB I add a lookup field to and I'll be dealing with relookup issues, right? Why script a relookup in all dBs with a PayerName lookup field, when I could just script 'create new record' and be done with it?

Thanks for your thoughts on this - I really appreciate all the help I get on this forum but I'm still not convinced that it's worth adding calcs or fields to resolve this problem.

LaRetta

Put it elsewhere, not in portal!

For ex as the caption for the portal.

Dj

Hi LaRetta,

The issue here is indeed an important principle of the FileMaker relational data system. And of relational data design generally.

A portal functions in tandem with a relationship. A single relationship.

All the elements placed within the portal must be sourced via the one relationship, and that *must* be the same relationship that the portal itself is based on.

If you'll pardon the fanciful metaphor, relationships are like threads that connect your files. Try to put two 'threads' into the one portal and they tangle and the portal does somersaults.

Portals are not designed to relate in more than one direction, and their logic breaks (in fact all logic goes out the window! ) if you try to make them do so.