designing a form for quotations

[jmat]

i'm trying to design a form that can be filled out to provide quotations for projects and am having trouble wrapping my head around the basic design. i have some experience developing relational databases in web applications but am new to FM Pro.

i want to have a form with a series of checkboxes. each checkbox corresponds to an option. the user checks off which options are desired and prices for all checked options are added together to get a total price for that project.

i was envisioning something like:

Database 1 - Options (Option ID, Option Name, Price)

Database 2 - Form (Option ID, Total Price)

i would create a relationship based on Option ID. but i don't know how to pull the list of options into the Form with checkboxes and prices. i tried using a portal but it didn't seem to work. and even once i do get a list into the Form, how can i use the checkboxes to help summarize the price?

any tips, advice, or resources would be greatly appreciated. i've tried reading through the user's guide and looking at some templates but just wind up getting frustrated.

thanks in advance,

matt

[danjacoby]

It's a little foggy, but let's see:

DB1 contains the list of options and their various prices. DB2 contains the actual orders.

OPTION 1: A Portal

To use a portal, you need DB3, which contains the "line items" created in the portal in DB2. There are two relationships: one between DB2 and DB1, based on the option, so that the option name and price come in automatically; the other between DB2 and DB3, based on a unique Order Number in DB2 (so you'll need an OrderNumber field in DB2 that is validated to be unique, and a corresponding OrderNumber field in DB3), so that the line items can be created.

With this method, you won't be able to use checkboxes, but the OptionID field in the portal can be formatted to be a popup list, pulling the contents of the OptionID field in DB1.

Finally, create a calc field in DB2 that is the sum of the related Price field in DB3; that will give you the total.

**********

OPTION 2: No Portal

Forget about DB1.

Create three fields for each option; call 'em "OptionID", "OptionName", and "Price" -- the last two being calc fields that return, respectively, text and number.

OptionName returns the Option Name for that Option ID if the corresponding OptionID field is not empty; Price returns the correct price if the corresponding OptionID field is not empty.

On the form, line all of the OptionID fields up, setting each up as a checkbox field with the value list containing only the OptionID for that field. The OptionName fields and the Price fields can be lined up in separate columns as well.

Finally, create a calc field that totals all the Price fields.

[Ugo DI LUCA]

Hi,

Yep there are limits with checkboxes...

I've seen a couple of samples and attachments here for those who wants to insist on this. One was from Ray (Cobalsky) and as usual brilliant, but needed an update script.

Search the Attachment section at the "Entrance" for files called "Allegation" and "Accusation" if I can recall.

Now, as Dan said, you will need a line item file (his DB3) and I would favor his Option 1, as there are some chances that a Quotation could be the start point of other Operations. Therefore, having calc fields all along the db could be tedious and you'll better use a separate file (DB1) to list the Options.

Lately, the whole process could become scripted from the DB1, picking some options from a portal which lists all options (as a Add to Kart method) into global fields, each option when confirmed ending in a separate line in the Line Item file.

[Korky]

Whether you do this as a flat file solution or using a related Line Items file, there is still the problem of getting conventional checkboxes to work as you visualise.

The way I got round a similar problem was to define a "Quantity" field and to fake a checkbox. This I did by formatting the Quantity field as Boolean and showing non-zeroes as "X" and zeroes as null. I then formatted the field as a button to run a setfield script using a Case step to toggle between 1 and 0. A calc field "Extension" stores the result of Quantity*Price. Prices are in Global Fields. In a flat file solution you have to define a set of fields and a script for each option.

[Korky]

Here is a sample file to illustrate my previous post

Options.zip