How to get data from a specific record in a one-to-many relationship

[KyleVogel]

I need help.

Two tables - ORDERS ("one" - every transaction by a customer) and ORDER DETAIL ("many" - each line item in each order, maybe 8-10 items per order).

What I want to do is find, from among the ORDER DETAIL records for order "X" the one where ITEM NUMBER equals "Y" and return the value for that ITEM QUANTITY.

In other words: For ORDER NUMBER 123, and ITEM NUMBER 321, the ITEM QUANTITY is 5. (And quantities for every other item as well.) Clearly, I can run a FIND fo these parameters, but I want the data to populate a form automatically

The layout I'm using has (needs) every ITEM listed (we only offer 14 ITEMS each month), and I'm trying to populate the ITEM QUANTITY per order.

[Leb i Sol]

look into direction of portals....your relationship is already there.

All the best!

[KyleVogel]

I'm somewhat familiar with Portals, but how would I get the system to understand which record holds the key bit of data?

Each ORDER record is related to 4-14 ORDER DETAIL records (depending on what ITEMS the customer wants). What I want to do is find the ITEM QUANTITY for a specific ITEM.

For order 752, how many orders of Meat Lasagna are there?

Order 752 is related to 7 ORDER DETAIL records

2 units of Chicken Soup

1 unit of garden salad

1 unit of bread sticks

1 unit of garlic bread

2 units of red wine

2 units of meat lasagna

How do I get FM to understand I want the "2" from "2 Units of meat lasagna"?

In the same layout, I want to pull the number of units from each menu item, ordered or not. Number of ravioli orders, for example (none in order 752...order 763 has 3...) Number of cannoli, number of white wine...

What I'm building is an overhead view of each order showing every possible item and what was actually ordered by each customer.

[mr_vodka]

You should consider using 3 tables.

One for your order, one for your product, and the 3rd a join table that related the Order to the items. It will also store the qty for each item.

[KyleVogel]

The ORDER DETAIL table is the link between the ORDER table and the ENTREE (item) table

[David Jondreau]

Seems like you've got two issues.

1) Does ORDER DETAIL contain a field with text as in your post? It needs to have the fields Quantity and Ingredient. If you have no way of controlling the Entry of that field (grabbing text from an online recipe eg) you need to get those values via a text calculation. How to do that is dependent on how standardized our ORDER DETAIL field is, but look up the functions Position, Middle Words, Filter among other Text Functions.

2) You also want a list of all non-used ingredients. That's going to take scripting the creation of ORDER DETAIL records, one for each ingredient with a quantity of 0.

[KyleVogel]

Grip,

1. The ORDER DETAL data is standardized and linked to the ENTREE table for names and such.

2. That's an interesting idea - I wonder if this next bit will screw that up, though...

The ORDER DETAIL data is imported, never created one record at a time.

I'm starting with a FM consultant to solve this issue - we'll see how that goes.

Thanks, everybody, for the tips.

[mr_vodka]

Well if in your data that you are importing has a column, field, or whatever for a specific item (entree) and it has a value for amt ordered and is just blank if that item is not ordered, then on all the matching fields, you can have a calulated value of something like.

Case ( IsEmpty ( Item1 ); 0 )

On the import yuo must have it selected to perform auto-entry. Then for all records you import, anything that has been ordered will have a number and anything that is blank will result in zero.

So what is the process of the data being imported?

Edited October 31, 2006 by Guest