Can't see the forrest for the trees

[hillborne]

Hey all,

I'm working on my second database and coming off a filemaker bender of twenty five consecutive 15-hour days. My mind is fried and I'm missing a very simple concept that's causing this problem, I'm sure.

I have learned an important word this month, though: featurecreep.

Based on the attached picture, I'm hoping someone can guide me through the correct way to set up a quote based on a quantity of products a customer has chosen. My trial and error approach to fix this failed.

I'm going to try to sleep and hope not to see the relationship graph in my dreams...again. If anyone can help, they might just save my sanity.

[Vaughan]

It seems to me that you're building the data structure to support an interface model. Display in 12 portals on a second monitor?

1) Get the data structure to meet the business needs.

2) Design an interface that the users can work with.

3) Work out how to get the interface to work with the data structure.

I usually build a "no compromise" data structure, then design an interface that has no regard for the data structure, only the needs of the users. The fun part is then getting the two to work together LOL.

In practice, if the data structure is correct then an easy and intuitive interface is easy to build because all the data is in the right place at the right time.

[hillborne]

Hi Vaughan,

The 2 monitor interface works and the 12 portal matrix acts just like a website with 'next page, previous page, goto page,' etc.

The data model was working and at first even the 'quoting' function seemed to work.

It wasn't until 2 customers liked the same product that I saw the problem of the quantities of the 'top most' customer in the portal being used all the time.

I'd imagine that what I'm trying to do isn't that different than a Customer-Invoice-Product database?

If anyone has experience doing calculations through join tables, they might be able to help me. Where do you have the calc fields and from what perspective do they calculate?

It seems to me that you're building the data structure to support an interface model. Display in 12 portals on a second monitor?

1) Get the data structure to meet the business needs.

2) Design an interface that the users can work with.

3) Work out how to get the interface to work with the data structure.

I usually build a "no compromise" data structure, then design an interface that has no regard for the data structure, only the needs of the users. The fun part is then getting the two to work together LOL.

In practice, if the data structure is correct then an easy and intuitive interface is easy to build because all the data is in the right place at the right time.

[comment]

I don't understand your description, and your RG is unreadable.

In general, an invoice (or a quote - same thing, really) has multiple items in a Lineitems child table, joining it to Products. This shouldn't change, no matter how the products are being selected. The quantities are stored in the LineItems table, and the totals can be done either by a summary field in LineItems or by a calculation field in the Invoices table.

I see no reason why 2 customers liking the same product would create a conflict - unless your data model is flawed.

See also:

http://fmforums.com/forum/showpost.php?post/355429/

[hillborne]

Hi Comment,

That example and your explanation are very helpful, thank you!

I think the flaw in my model is that I'm running the calcs and summary on a layout based on the product table. I would do a search in products to find all products that are related to a customer and then switch to a quote layout based on products.

If that is the flaw, can you explain why the calcs only select the quantity that is 'first' in the join table? I would just like to understand the fundamental mistake I've made.

Since I don't need to track multiple invoices/quotes, would you agree that I can use the CONTACT table instead and put the calcs there?

Once again, thank you for your help.

I don't understand your description, and your RG is unreadable.

In general, an invoice (or a quote - same thing, really) has multiple items in a Lineitems child table, joining it to Products. This shouldn't change, no matter how the products are being selected. The quantities are stored in the LineItems table, and the totals can be done either by a summary field in LineItems or by a calculation field in the Invoices table.

I see no reason why 2 customers liking the same product would create a conflict - unless your data model is flawed.

See also:

http://fmforums.com/forum/showpost.php?post/355429/

[comment]

Since I don't need to track multiple invoices/quotes, would you agree that I can use the CONTACT table instead and put the calcs there?

I would agree - IF a contact has only one invoice/quote ever. In fact, in such case a contact IS an invoice/quote; it's only a matter of convenience what you call such record.

can you explain why the calcs only select the quantity that is 'first' in the join table?

In a calculation, a reference to a related field gets the data from the first related record (in the sort order of the relationship). When you are in the Products table, all the line items look the same, regardless of who is the customer on the other side of the join.

It would be different if you had a global gContactID field in Products and used it to establish another relationship to LineItems. But you can already view the same thing from the Contacts table, so I don't see why you would bother with that.

[hillborne]

When you say:

In a calculation, a reference to a related field gets the data from the first related record (in the sort order of the relationship). When you are in the Products table, all the line items look the same, regardless of who is the customer on the other side of the join.

It makes me concerned that I can't do the grand total calculation from the customer table because it would still be trying to refer to quantities from the join table and would still only pull the first related record.

So if you have just switched perspective in a many-to-many relationship, it'd still be 'broken.'

Does that mean you really have to put all the calculations in the join table and the 'quote' is a layout based on that join table? Ug, still confused.

[comment]

No, because if you use an aggregate function, it will take into account ALL related records. For example:

Sum ( LineItems::ExtendedPrice )

will return the total price for all the items of the customer - when placed in the Customers table. The same calculation, when done from Products, would return the total gross from sales of that product.

[hillborne]

Hi Comment,

That sounds promising!

So, for final clarification:

On LineItems I will have a subtotal calculation that takes the retail price from Product and multiplies it by the quantity in LineItems.

Then on the customer/quote table I use SUM on that subtotal calculation?

I just didn't know how to put together the three fields and three tables (Price, Subtotal, Grandtotal vs Customer, Line Item, Product)

Thanks, again. You have been super helpful.

No, because if you use an aggregate function, it will take into account ALL related records. For example:

Sum ( LineItems::ExtendedPrice )

will return the total price for all the items of the customer - when placed in the Customers table. The same calculation, when done from Products, would return the total gross from sales of that product.

[comment]

On LineItems I will have a subtotal calculation that takes the retail price from Product and multiplies it by the quantity in LineItems.

Then on the customer/quote table I use SUM on that subtotal calculation?

That is almost correct. I mean, it would actually work - up to the moment where you change your prices. That's why you need to lookup the (current) price from Products into a Price field in LineItems.