Self Join with a portal question

[Brewser]

I have a card collectors database with 2 tables one for cards and one for sales.

The sales table is a one to many relationship to cards table.

I also have a self join to cards table to do lookups by series of cards. They are related by 'series'.

I have 3 fields in the sales table that is setup as a portal to my cards layout - sales date, sales qty, sales amount. When the user is on a certain card, they can enter multiple sales for each card.

I have created a couple sales reports, one that will show you all cards sold sorted by series and another that the user can select the series and it will list all cards sold by that selected series.

If the user selects the show all sales report, it will show them the same card info for each sale of the card repeatedly until it runs out of sales for that card and then do the same for each card with sales info and so forth. (example) If I have a card with 3 sales it will show me that card 3 times on the report with each of the sales info related to that card)

Here is my problem,

If I have the user select a series it will only show the most recent sale for the card and go on the the next card. If I have 3 sales for one card, it will only show me the most recent and go on to the next card and only show me the most recent sale for next card and so forth.

Why is it doing this? What do I need to add to my script to have it show each card for each sale when looking up by series?

In my script I have it show only related records matching the self join table and the report layout.

Then I do a constrain found set to find all sales qty with >0 (from sales table).

What am I missing in the script? Does it have to do with the way the relationship is setup?

Thanks

[-Queue-]

Can you attach a zipped sample of your file?

[Brewser]

My database, even with removing records, is too large to attach. So I have created another DB that works the same way.

This will give you the same results.

Thanks for your help on this.

cardcollector.zip

[-Queue-]

Your layouts are based on two different tables. Sales Report is based on Card Sales, while Sales by Series is based on Card Collector. Is this intentional?

[Brewser]

I did this while playing around. I found the complete series report only worked the way I wanted it to with the card sales layout. If you change it to the card collector layout it will only show 1 card with only 1 sale. When you change it back to card sales layout, it will list the card for each sale.

This is driving me crazy.

Thanks

[-Queue-]

Well, that makes sense, because there are only unique cards in the Card Collector table. Any sales reports must be done from the Card Sales table or only one will be shown, unless you're using a portal to display them.

I would think you first want to Go to Related Record based on Series and, loop to set a global ID field with each record's ID (or Copy All Records on a layout that contains only the ID field, and Paste into a global), then Go to Related Record based on Card Sales. That way you are first finding records with the same series and then finding the related Card Sales records for each Card's ID.

See attached for modified file.

cardcollector.zip

[Brewser]

That worked great. Unreal!!! I would have never figured that out and I have already spent about 10 hours trying every possible scenerio.

I am a little confused on a couple variables:

You have set field with a "" - what does that accomplish?

You also have another set field with two fields and a paragraph at the end. What does this accomplish?

I noticed you use halt script. How is this different than exit script?

You have the one related records set to the current layout. This is confusing since the current layout at this point of the script is the reports menu, why wouldn't you go to the card report layout instead?

It all works great, I guess I am a little confused on why.

You have been a great help. Thanks again.

[-Queue-]

gCardID is related to CardID in the Card Sales table via the Card Sales 2 relationship. It functions as a multi-key, meaning that if there is more than one record found for a given series, it is used to capture each CardID, separated by a carriage return (

[Brewser]

Good stuff. Thanks again for your help. Is there a good book or online course that would teach this stuff or is it just from playing around. I thought I was getting pretty good at filemaker and you brought me back to earth.

[-Queue-]

I learned about multi-keys from several online forums. There are some books for version 7, as well as at least one training course that you can purchase. Do a search on this site for 'training'.