Hello, could someone help me with a solution for this.

I have a portal which shows various Films a client has purchased, the relation is based on the word "sold" in an "Actions" field so I have an "if" function to filter this, which works fine.

Now for various reasons a Film may have the word "Sold" in the "Actions" Field more than once, so I end up having the same Film in the portal more than once.

My question is how can I "filter" this, so that the Film in the portal only appears once.

Thanks in advance

Ian

Ian->

Welcome to the Forums!

Make a calculation for the relationship which will look in the Actions field for the word "sold":

ActionContainsSold = Position ( Actions ; "Sold" ; 1 ; 1 )

or

ActionContainsSold = PatternCount ( Actions ; "Sold" )

Some folks prefer Position, others prefer PatternCount. Either will return a number > 0 if the word Sold appears anywhere in Actions.

Sounds like you actually have more than one record for a given film where "Sold" is in the Action field rather than one record for a given film with "Sold" in the Action field multiple times. Correct?

Thanks for your replies

Sounds like you actually have more than one record for a given film where "Sold" is in the Action field rather than one record for a given film with "Sold" in the Action field multiple times. Correct?

Yes thats correct, CyborgSam's suggestion "Position ( Actions ; "Sold" ; 1 ; 1 )" gave the same result as my "if" function

Ian

Assuming you are using at least FileMaker Pro 7. Take a look at the attached Example file.

Example.zip

Not quite, please have a look at my example,

you can see on the List Page sale "5004028" has 2 "Sold" entries for different reasons,

but I would like to filter this down to 1 entry in the portal on the Statistics page.

Film_Example.zip

Edited September 23, 200520 yr by Guest

OK. I can't think of a way to do want you are looking for without scripting, though I imagine someone may.

I attached a edited version of you file. Basically, when a film is sold you click on the sold button. When you delete a film, you click the delete button.

This method won't help you with the existing records only newly added ones. You could write a looping script to update the relatedRecordNumber field in the existing records.

In version 7 there is a way to use an auto-enter calculation to produce a mark on only the 1st "Sold" record. But it requires maintenance, if you edit or delete that record.

Notice that the SoldMark field has "Do not replace" checkbox off, for the auto-enter calculation.

Personally I think there should be a "DateSold" field, which only has an entry for the 1st date sold. I've had much trouble fixing other's solutions that used "Status" fields. They are often plagued by data entry omissions and ambiguity, such as you are seeing.

Film_Example2.zip

Edited September 24, 200520 yr by Guest
do not replace off

Thanks for everyones help here, But now I think the logical answer is to get my client not to enter "Sold" until it really is Sold.

Thanks again

Ian