I am creating an order entry piece for a client and they requested that all of the options for each item be displayed in a checkbox list so that the order takers can see all the options and check the ones that the client requests. I know how to display the list but I do not know how to use the selected options to create a new subtotal (e.g. If I select an item and two options, I need the price of the item to increase based on the price of each option. Cost $100, Option A=$10, Option B=$8, Option C=$15. User selects options B & C, price should reflect the new cost of $123 ($100+$8+$15).

Relevant Info.

Order Entry DB where this functionality needs to be implemented.

There is an Inventory DB that provides a list of catalog items and their possible options

There is an Options DB that contains all possible options and their respective prices.

Thank you in advance for your help.

Original cost = cost and options are as stated the following calculation will give you the results in the NewCost field. Options can be in a value list with the full description (Option A = $10)

Cost + Case(PatternCount(Options,"Option A")=1,10) + Case(PatternCount(Options,"Option B")=1,8) + Case(PatternCount(Options,"Option C")=1,15)

Take the following:

Order.fp5 has Order entry fields, including a field for "Options". The Options field is simply a text field with the options in it seperated by paragraph returns, as is standard with any checkbox formatted field.

Option.fp5 has the option data in it. It has fields such as: OptionLetter, Price, Description, etc.

Setup a relationship between Order.fp5 and Option.fp5 by Options::OptionLetter. This will establish what is called a multi-line key on the left side of the relationship, related to the OptionLetter on the right side of the relationship. This relationship will return all the records for the options specified in the order.

Then in the Order.fp5 file, create a simple calc to sum the totals:

cOptionTotal = Sum (Option.fp5 by Options::Price)

I understand your logic and understand how it could apply to a checkbox but that solution would require hard-coding. I wanted to have the client use a separate database file to manage options and their respective prices after I leave. Is this doable? My problem is that I've done MS Access development for 3 years so I just want to use SQL and VBA which I know I can make work!

Kurt -

I'm a little slow on the uptake, but I didn't know you could do that. (Or if I knew it in the past, I'd forgotten it.)

How does FM keep this straight??? Hard returns = consider text element to be different values for relationship?? I would have thought it was looking to match the entire field, not pieces of it.

Crikey, it also works on the "right" side of the relationship, too. Where have I been all this time?

Holy moly. Kind of opens up a lot of "options" in my mind.

Thanks.

Charlie

[ October 12, 2001: Message edited by: Charlie ]

Kurt -

I'll have to see what all this means, but my head is already thinking back to some very unpleasant work-arounds that I used last year on one project.

It seems that you could use a multi-line field as a sort of repeating field in some cases, but still allow that field to be related. Are there limits to the number of "lines"?

C

quote:

---

Originally posted by Charlie:

I'll have to see what all this means, but my head is already thinking back to some very unpleasant work-arounds that I used last year on one project.

---

I do this kind of stuff all the time. This is a really clean way to do alot of different stuff, eliminates all those nasty work-arounds you might have done in the past.

[/QB]

It is all about indexing.

Filemaker indexes both "words" and "lines". Words are characters seperated by spaces, lines are seperate by paragraph returns.

Now for relationship purposes Filemaker matches on lines. So Filemaker will related every line in a field to every line in another field. So you can related LINE to LINE or MULTI-LINE to LINE or LINE to MULTI-LINE or MULTI-LINE to MULTI-LINE. Pretty powerful stuff huh?

The only thing to keep in mind is that Filemaker only indexes "words" that are 20 characters or smaller, and "lines" that are 60 characters or smaller. Notice that I did not say "up to", as soon as you have a word more than 20 characters or a line more than 60 characters, Filemaker will STOP indexing. So make sure that all of your words are smaller than 21 characters and lines smaller than 61 characters.

Kurt -

Many thanks. I feel like I've just found a door behind a cabinet inside a house I've lived in for ten years.

C