Calculation, Summary? I'm confused!

[spankalee]

Hi,

I'm pretty new to FileMaker, having worked mainly with SQL DB's in the past. I'm trying to do something that would be simple (to me) with SQL but it really baffling me with FileMaker.

I have a database of Orders, which has fields like OrderID, Date, Placed, and Shipped. Then I have another database called OrderItems, which has OrderID, PruductNumber, Pieces, and OnOrder. OnOrder is supposed to be the sum of OrderItems::Pieces for all Orders that have been placed, but not shipped.

I have a Layout in Orders with a portal to OrderItems, the portal has the fields ProductNumber, OnOrder, and Pieces. The idea is that when I type in a product number into the portal, I want to know how many of the same product are currently on order.

For people who know SQL here what I would do:

select sum(OrderItems.Pieces) where OrderItems.OrderID = Orders.OrderID and Orders.Placed = 'Y' and Orders.Shipped = 'N';

So far I've tried Caculations with Sum(), Summary Fields, GetSummery(), Self-Joins, and all kinds of relationships and nothing works. I created a third table relating to OrderItems by ProductNumber where I tried to do the calculation.

Any ideas on how I could do this in FileMaker, do I need a script? I couldn't see any script items that looked like they could do this.

Thanks for any help...

Justin

[spankalee]

Ahh... another attempt failed. I was reading more about self-joins, and tried this. I really though this one would work.

In OrderItems:

OnOrderMatch = ProductNumber & Orders::Placed & Orders::Shipped

OnOrderMatchConst = ProductNumber & "10"

Then I tried to define a self-join relationship between OnOrderMatchConst and OnOrderMatch, that way any OrderItem would be linked to all other OrderItems of the same ProductNumber where Placed = "1" and Shipped = "0", exactly what I want. But I couldn't make the relationshop work because OnOrderMatch can't be stored, and therefore indexed, because it references external fields.

So here's a new question: What If I used a lookup for OrderItems::Placed and OrderItems::Shipped instead of a calculation? I think this would work, but what happens when the fields are changed on the Order? Will the change be reflected in it's child OrderItems?

[LiveOak]

You've got the right idea. You must create a relationship based upon a calculated key that gives you the total of items with the same product number that are on order. This relationship needs to be a self-relationship from the Order Items file to the Order Items file. In the order items file:

The field on the left side of the relationship is:

OnOrderMatchConst = ProductNumber & "10"

The field on the right side of the relationship is:

OnOrderMatch = ProductNumber & Placed & Shipped

Call the relationship SelfOrderItems

Again in Order Items, your total field for items on order but not shipped is:

ItemOrderNotShipped (calculation, number) = Sum(SelfOrderItems::Qty)

This is a field IN Order Items and can be display on a line of your portal in Orders just like any other field from Order Items.

-bd

[spankalee]

Thanks for the quick reply...

After looking around some more I think I posted this to the wrong Forum, I guess this is more of a relationship problem than a summary or calculation problem.

I actually have it working now except for that if I change the shipping status of an order the change does not get reflected in the related records of OrderItems.

I'd like my definition of OnOrderMatch to be:

OnOrderMatch = ProductNumber & Orders::Placed & Orders::Shipped

but that won't work because then it can't be indexed and used in a reference.

Some one else had this problem and a link was posted with a solution, but now the link doesn't work, anyone know how I can find this topic?

http://www.fmforums.com/ubb/ultimatebb.php?ubb=get_topic;f=17;t=000374

the thread this was in is here:

http://www.fmforums.com/threads/showflat.php?Cat=&Board=UBB17&Number=15955

-Justin

[spankalee]

Wow... I can't figure out how to do the simplets things in FM....

So like I said the match is working now, but OrderItems::Placed doesn't update when then related Order::Placed field changes.

Ok, as is mentioned elsewhere in the forums, I need to do a relookup. So here's what I did, but I can't get it to work.

The Orders::Placed field is a yes/no, so I created a button called 'change' which toggles the field and then performs a relook up. First I tried Relookup Contents["OrderItems::Placed"] from with in a script in Orders. No luck, didn't update OrderItems. Then I tried Relookup Contents["Placed"], still no luck. Then I made a script in OrderItems that did Relookup Contents["Placed"] and called it from the script in Orders. Still no luck, and I get an error saying

"There are no fields that lookup values based on the field "Placed"."

So I try and do it manually with the Records>Relookup Contents menu and I get the same error. It doesn't matter if I do it from Orders or OrderItems.

I think I'm missing something here...

-Justin