multi-criteria relationship in portal not working

[julesw]

hi there,

i am very new to portals, and not a trained developer by a long shot, so please bear with me...

i work for a small independent newspaper. we have a large contacts database, in which i have added a table to track content in the paper (editorial and advertising).

i want to link these up, so it's possible to see what articles were sent by a particular company, or what advertising they have done.

i have two tables: contacts (the parent i think?) and content (the child?).

because i need to be able to store the article information separately from the advertising, i have created two different relationships.

the contacts database auto-enters a unique ID for each record.

i have created a field in the content table where this can be entered.

the contact database also has a field called 'contributor' (a checkbox which is selected if they have sent us articles), and i have created a field by the same name in content, which is a calculation field that produces the word contributor if the item description is one of a few values (eg article, picture story etc).

i have got the same kind of set up for advertisers - a checkbox in the contacts database saying 'advertiser' and a calculation field in the content table which produces 'advertiser' if the item type is ad, promotion, logo etc.

i have created two relationships:

the first is called 'article content' and uses the contributor match and the ID number match

the second is called 'ad content' and uses the advertiser match and the ID number match.

for some reason, these are not working - or, i should say, working intermittently.

that is, i set all this up in a copy of the database file, and it seemed to eventually work.

but when i copied the exact same solution into the real database, it didnt.

either it displays all the records, regardless of whether they are ads or articles OR it only displays one record, when the portal is of course set to show several rows.

also, although i have ticked the box to allow new records to be created in the content table, it tells me 'the field cannot be modified' on all the fields in the last row of the portal when i try to do this.

this is an important function to have, because i want for example, sales people to be able to add ad bookings to the relevant company record, rather than expecting them to copy the ID, go to the content table, paste it in and add the details there.

it's at that hair-tearing stage, so any help will be so very appreciated....

[TougToug]

Hi there, i am quite new to filemaker and databases in general myself (couple of months) but i came across the same sort of problem with my portals

I tried to write a solution but it got very messy and i dont think you would have understood me, so i'm gonna leave it to the experts, they'll help you definitely

sorry cant help

[julesw]

please help, someone!

[-Queue-]

I have deleted your double-post and would encourage you not to double-post in the future. You already bumped this thread up by posting again, and there's no reason to have two simultaneous threads for the same subject.

It may help you to know that auto-enter calculations or static data are only entered when you create a record, unless you have 'do not replace existing values' deselected, which you would not want to have for a primary key. When you implement such solutions in an existing database, you need to run a Set Field loop or Replace Field Contents with all records found, to update existing records with the new auto-enter option. If you do not, then the field will be blank in all existing records.

If you have verified that existing records are set correctly, then please Zip and attach your file, listing the tables, layouts, and fields that are causing problems.

[julesw]

okay, thanks to the 'Key Concepts in FileMaker7: Mapping New Territory' article <http://www.digfm.org/ref/FM7_key_concepts.pdf> that someone posted a link to in another forum, I know understand much more about how relationships and portals work in FM7. i realise that each layout uses a specific relationship, and the portal contents will only display correctly if the table occurrences are all related correctly. I have completely redone my relationships graph as a result!

so ive got what i wanted: one layout that shows all the ads an organisation has placed and another that shows all the articles they have sent. (it would have been ideal to have these two separate portals on the same layout, but i gather this is not possible.)

the only problem i am still having is that it will not allow creation of new related records within the portal, even though i have selected this option.

is there something simple i am missing?

[-Queue-]

Is the child key perhaps a calculation instead of a normal text or number field?

[bruceR]

Queue, your comments on auto enter calcs match popular belief but they are actually not correct. The calcs will ALWAY evalaute every single time any of the dependent fields gets edited. If the fields is ever empty, it will get repopulated. Here is the tricky and revealing part: if there are lookups based on the auto-enter field, they will be retriggered every time any dependent field changes, even if the auto enter field does not change value.

[Søren Dyhr]

Bruce is right - There's more to it: ....If you search for the word "unnoticed" in the Resources dept. of JMO's site, will following template show up:

http://www.filemakerpros.com/Related_Match.sit

http://www.filemakerpros.com/Related_Match_Field.zip

Eventhoug the template is in ver 5.0 is it still the way relations work, under these conditions.

--sd

[-Queue-]

Bruce, you are correct that the field is reevaluated, if emptied, (unless the auto-enter calc references the field itself) which makes sense since it is similar to entering a value in a new record. However, it does not reevaluate if a dependent field is changed without the auto-enter field first being nullified.

In this case, however, I was merely warning that adding an auto-enter option to a table with existing records will not auto-update those records. Manual intervention is necessary to populate existing records with the auto-enter option.

[julesw]

hi all,

thanks so much for your help so far. i am just keeping up, i think!

ive now got 3 relationships set up between the tables <contacts> and <content>:

1. content

2. ad content

3. article content

all three use [record ID] from <contacts> as a match field. the first uses that alone, simply looking it up and inserting it in the field [contacts ref].

the other two use an additional field which relates the records if they have matching values (saying either 'contributor' or 'advertiser').

i am working on the assumption that to be able to create a new record in the content table from within the contacts table, i need to make the field in the content table [contacts ref] auto-enter the [record ID] from the contacts table.

the complication is that i need it to do this for all 3 relationships. and of course, it can only do a lookup based on one relationship.

do people understand my problem?

ive just created calculation fields called [ad contact ref] and [article contact ref] which simply auto-enter the value from [contacts ref].

but i still cant create new records in the layouts that relate to these 2 relationships, only in the general one based on the 1st relationship.

why would this be??

(i saw the comment above that there might be some problem when the match field is a calc field, and i did try doing it as a lookup, but it didnt seem to work. i may have been doing it wrong though, so any tips on exactly how it should be done would be great if you think this is causing the problem.)

please go slowly with any explanations, this stuff is all very new to me!