filtering many to many relationship

[burdenslifted]

Working in FM 10...

I have a database with a standard many-to-many relationship

orders--products

for CD sales, but also a separate database that lists individual songs. Each song record has a UPC code that corresponds to a CD's UPC code found in products. I need to generate a report on sales by song during a certain time period. How do I set up a filter based on order date for a productlineitems portal on a layout based on songs? Or is there a better way to do this? The data needs to be combined with other sales info, so I'd like to avoid a search method.

[TheTominator]

One trick you may explore is to create an unstored calc in your starting table (the one used by the layout).

This unstored calc would use List() and contain a list of all of the record IDs in a related table.

This gives you another tool for wiring up your relationships to make a filtered portal.

[burdenslifted]

I can see how that might be useful, but I'm not sure how to apply it here. Which related table would the List field store record ids for? I'm not sure how that would help me relate songs and orders (or productlineitems), since the only match field between the two databases is UPC, found in the Products table. Can you be more specific?

[TheTominator]

To be specific, I'll need to work with specific fields and tables.

Let's say you have four tables defined this way. I have used product_id instead of UPC.

PRODUCT

------

product_id

ORDER

------

order_id

order_date

PRODUCTLINEITEMS

------

order_id

product_id

SONG

------

product_id

gOrderDateStartRange (global used to filter portal)

gOrderDateEndRange (global used to filter portal)

cOrderIDsInDateRange = List(song_ORDER::order_id)

SONG is connected to song_ORDER (a TO of the ORDER table) using both gOrderDateStartRange and gOrderDateEndRange to limit the dates of the orders. This provides a list of all orders in the date range without regard to the song being present on the order.

Now wire up a new relationship to display in your portal.

SONG is connected to song_PRODUCTLINEITEMS__FilteredDateRange using (product_id = product_id) and (cOrderIDsInDateRange = order_id)

[burdenslifted]

You've got the setup just right, and this looks like a good solution. I'll give it a try! Thanks.

[burdenslifted]

Finally getting around to trying this, and feeling dull-witted. In particular (cOrderIDsInDateRange = order_id) doesn't seem right, because the calc field holds a whole set of order_ids which will never be an equal match for the single order_id in the productlineitems record. How do I get it to see the order_id as a match if contained in the calc field?

[TheTominator]

In particular (cOrderIDsInDateRange = order_id) doesn't seem right, because the calc field holds a whole set of order_ids which will never be an equal match for the single order_id in the productlineitems record.

The way FileMaker relationship matches work does not require an exact match of the entire value on the left and right side of the relationship. It will find a match when any item in the list matches the other side. In other words if any of the order ids in the list match the single value on the other side, it will say the record matches.

[LaRetta]

Hi burdenslifted,

"I need to generate a report on sales by song during a certain time period. "

Can you help me envision the report results? What if a song is on two CDs, such as Purple Haze is on Smash Hits and also on Are you Experienced. I assume your lineitems charge Quantity x CD price. So how can you produce a report on 'sales by song' when there are multiple songs per CD?

Do you want to count how many songs are on that CD to get the average song price? If you list the CD price on every song attched to the CD, your sales figure will be incorrect.

I assume you don't want songs listed even if no sales occurred but can you verify?

Please produce a sample report showing how you want your data grouped and what the body of the report should contain and how the sales amounts should be summarized.

The report request will dictate the approach taken to achieve it. :wink2:

Edited April 17, 2010 by Guest
Added last sentence

[burdenslifted]

Good to know -- I guess I can't blame the fact that it's not working on that then!

[burdenslifted]

The object of this report is to calculate royalties owed to publishers & artists. The way the database is set up (not my doing), each instance of the song has a different record, so Purple Haze would have two entries with two different song_ids, one with the UPC for Smash Hits and one with the UPC for Are You Experienced. (Nice example, btw.)

The report needs to be grouped by publisher, album, and song, reporting the quantity of sales for each song (not the dollar amount). The qty is multiplied by the royalty rate to calculate total royalties due. Every song should be on the report, whether there are sales or not.

Sample report attached.

Seems simple. It's currently being done by performing a search within the sales database, generating a summary report, then adding sales figures by hand to an excel file with the royalty information. Seems to me FM should be able to retrieve the sales data and report it directly from the songs database (which has the royalty info). Am I crazy?

royalty_sample.txt

Edited April 17, 2010 by Guest

[LaRetta]

Okay, I believe I have one final question ...

where does the Royalty rate come from? It can't come from Publisher because EH has multiple CDs and songs at different rate. It can't come from album because Are You Experienced has different rates for different songs. And it can't come from Song table because Purple Haze has different rate on each of them.

[burdenslifted]

The royalty rate comes from a field in the SONG table, input by hand. To refine TheTomintor's schema to make it more in line with what I've actually got:

PRODUCT

------

_kp_product_id

UPC

ORDER

------

_kp_order_id

order_date

PRODUCTLINEITEMS

------

_kf_order_id

_kf_product_id

SONG

------

_kp_song_id

song_title

publisher

album

UPC

royalty_rate

gOrderDateStartRange (global used to filter portal)

gOrderDateEndRange (global used to filter portal)

cOrderIDsInDateRange = List(song_ORDER::order_id)

Because the publisher and royalty rate might differ with each instance of the song's publication, each instance is listed as a separate song in the database, e.g.:

1

PurpleHaze

EH

SmashHits

111111111

.09

2

Fire

EH

SmashHits

111111111

.09

3

PurpleHaze

EH

AYE

111111112

.21

4

ManicDepress

EH

AYE

111111112

.07

5

Hello

Elek

DoorsGH

111111113

.15

6

LightMyFire

Elek

DoorsGH

111111113

.15

Clear as mud now?

Edited April 17, 2010 by Guest

[LaRetta]

Here is one approach. Keep in mind that I consider myself far from being a Relationship Master and others may provide additional ideas other than mine and The Tominator's.

I found myself getting side-tracked wanting to attach the personnel, instruments (Jimi's guitar) and ...

It would be a fun concept to run with because there are so many levels and n:n joins!! One instrument can be played by many musicians and if you want to know all albums which use a Renard Long Bore Bassoon, only a good structure could produce the right results. Or if you want to find a drummer ... it goes on ... what fun!! :laugh2:

SongsReport.zip