Calculation across related tables.

[charisse]

I have written a calculation to display sales prices (from multiple tables) on my main product table. Each product is unique and has only one selling price, but it may have been offered in multiple sales events.

The calculation works, but I am wondering if this is the best way to approach the task.

Case ( not IsEmpty (Auction 16_04::Hammer) ; Auction 16_04::Hammer for Inventory ;
(Auction 16_02::Hammer) ; Auction 16_02::Hammer for Inventory ;
(Auction 15_11::Hammer) ; Auction 15_11::Hammer for Inventory ;
(Auction 15_09::Hammer) ; Auction 15_09::Hammer for Inventory ;
(Auction 15_07::Hammer) ; Auction 15_07::Hammer for Inventory ;
(Auction 15_05::Hammer) ; Auction 15_05::Hammer for Inventory ;
(Auction 15_03::Hammer) ; Auction 15_03::Hammer for Inventory ;
(Auction 15_02::Hammer) ; Auction 15_02::Hammer for Inventory ; "" )

We work in a peer to peer environment. Approximately 15,000 records in the main table and about 600-800 in the event tables.

Any input would be most appreciated.

Thanks!

[comment]

23 minutes ago, charisse said:

I am wondering if this is the best way to approach the task.

Probably not, but we have no way of telling for sure without understanding what this is about. All we see is a calculation that's meaningless to anyone not familiar with your structure. But the numbered tables hint at a seriously flawed structure.

Apparently, you have a many-to-many relationship between Products and Events - and if a product has a specific price for an event it participates in, that would be stored in a join table placed between the two - not subject to a calculation.

 

23 minutes ago, charisse said:

Each product is unique and has only one selling price

If a product has only one selling price, then what are you calculating?

 

Edited June 8, 2016 by comment

[charisse]

Let me try to clarify... I have a database of products each of which are unique and have a quantity of one. A group of these products may be offered in a sales event. Each sales event is it's own database. If a product sells I need to retrieve the price for which it sells and show that amount on the corresponding record in the products database.

I thought about the join table approach but it seemed somewhat pointless as all the record are related by the same id-field and I was still needing to "fetch" the actual selling prices with a calculation.

[comment]

12 minutes ago, charisse said:

Each sales event is it's own database.

That's what I suspected - and that is a major flaw,that will make everything else much more complicated than it needs to be. I also suspect you have a field for every product in each of the events tables? That too is a mistake.

 

15 minutes ago, charisse said:

I thought about the join table approach but it seemed somewhat pointless as all the record are related by the same id-field

Not sure what you mean by that.

 

16 minutes ago, charisse said:

and I was still needing to "fetch" the actual selling prices with a calculation.

Well, no. As I said earlier, if a product has an event-specific price, that would be stored in the join table, not calculated.

 

[charisse]

Yes, I understand the whole affair is quite complicated an convoluted. I am trying to make the best of what I have been given to work with.

I import only the needed records (products) to the specific event (600-880 per event).

I guess I am not understanding how to get the selling prices into the join table other than by calculation.

[comment]

30 minutes ago, charisse said:

I guess I am not understanding how to get the selling prices into the join table other than by calculation.

You're not telling us where the selling price is supposed to come from. I presume you would start by looking up the list price of the product, then adjusting it as necessary for the specific event?

Have a look at the attached demo.

ProductEventJoin.fp7

[charisse]

The selling price comes from the event. Each event is it's own db.

Each item/product is unique and can only be sold one time. The 15,000 records in the inventory/product table are 15,000 unique items. There are no list prices (not that this really matters).

I will study the demo and see if I can apply the principle.

Thank you.

[comment]

7 minutes ago, charisse said:

Each item/product is unique and can only be sold one time.

But can a product be offered at an event and not sold (and then offered at another event)? If not, or if you're interested in only tracking actual sales, then the relationship between Events and Product becomes one-to-many - and you won't need the join table.

 

12 minutes ago, charisse said:

The selling price comes from the event.

I am afraid that doesn't make sense to me.

[charisse]

Yes, I only need to track actual sales. And yes I agree that I don't need the join table.

My issue is that the price field originates in the event databases, (and there are 24+ events with approx. 4 new event databases created annually), not in the products/inventory table. If I put a field for the actual selling price on the record in the products/inventory table, the data will need to be "delivered" to it somehow.

If via the relationships then I would need to put a price field for the "actual selling price" on the product record in the products/inventory table for every event, something like:
Event 1::Price
Event 2::Price
but this seems a bit of a waste of space as the product will only be sold once and all of the price fields on the record will be empty save one.

or

I can put a price field for the "actual selling price" on the product record in the products/inventory table and have that field "go get" the price by calculation.

Not sure if one or the other would be "better," or if this approach even makes sense.

I apologize if this is difficult to follow. With my limited expertise in Filemaker I am not always aware of the proper syntax etc.

 

[Fitch]

If each product can only belong to one event, then store the ID of the Event in the Product record, along with the price.

[charisse]

Can belong to multiple events, but only sold once.

[comment]

33 minutes ago, charisse said:

Can belong to multiple events, but only sold once.

If this "belonging" needs to be recorded, then you do have a many-to-many relationship between Products and Events.

At this point, we need a better description of your workflow. What comes first: products or events? How do you add products to events - or events to products? At what point do you determine the price of a product?

One thing is clear though: events are data. You should never have to modify your solution's schema just to add data. Adding an event should be a simple matter of adding a record to the Events table.

 

Edited June 10, 2016 by comment