First-time poster...

[mzimmers]

...so I hope I'm in the right forum for this question.

I've been using FM PRO (v7) for some time now, for REALLY simple stuff. I'm now trying something a little more complicated, and can't figure out how to do it.

I'm building a DB to keep track of repairs to customer items. There is a handful of possible repair types, from which I want the user to select as many as are applicable. This I defined as a checkbox set. Now, I'd like to calculate the cost of the repair based on which boxes the user checks. For example, if he checks only "replace housing", the repair would be $150; if he checks "replace PC card" it would be $200; if he checks both, it would be $350, etc.

If it's readily obvious to anyone that I'm not using the appropriate constructs for this problem, please feel free to suggest other ways to go, too. Thanks...

[RalphL]

Checkboxes are a nice user interface but they store multi values which is considered bad in relational database design. The values are stored as a list, i.e., they are separated by returns. In you example when 2 boxes are checked the data would look like this if you displayed it in a text field.

replace housing

replace PC card

You can use the patterncount function to find which items are in the list.

The calculation would look something like this:

Patterncount(field; "replace housing") * replace housing cost +

Patterncount(field; "replace PC card") * replace PC card cost +

etc.

Patterncount will return a 1 if the pattern is in the field and a 0 if it isn't.

[mzimmers]

Hey, Ralph -

Thanks for the response. I've implemented the patterncount function as you indicate, but I never get a value other than zero in the total cost field. This leads me to conclude that one of two things is happening:

1. the text isn't getting stored as you say it should be

or

2. I'm not getting the results from the patterncount function into the new variable. Is there a a way to force this (an "update" button)?

Thanks...

----

*sigh* I really should check ALL my work before posting...I was using the incorrect field to search for the text. I think I'll leave this post here as a monument to my carelessness.

In any event, it seems as though it's working...thanks a bunch.

mz

Edited January 22, 2006 by Guest

[mzimmers]

I'm a little further into the design of the DB now. For those of you who agree that check boxes aren't the best way to go for this, what do you suggest as an alternative? I'll have a handful of fields that all need to be individually and independently selectable, and I will want to report on them.

Thanks so much...this place rocks.

[Kent Searight]

Creating a "Line Item" record for each service performed might be a better approach. In other words, create a table called "Repairs" or "Services" or whatever makes the most sense to you. Create a field in that table called RepairType. Then whenever you do a repair or service, create a new record and populate the RepairType field with whatever type of repair was done.

My explanation is greatly oversimplified...you'll also want to (probably) control the RepairType entries via value list, create repair records through a portal, assign IDs to each record in order to properly relate them to customers, POs, work orders, invoices, etc.

Hope this makes sense and helps! Feel free to ask more questions if it doesn't make sense.

[mzimmers]

Hi, Kent -

Thanks for the response. My current implementation has one table for the products, and another for the repairs. The product record has a short list of repair numbers that can (theoretically) be used to look up the related field in the repair table. I say "theoretically" because I can't get the find to work, but that's another story in another thread.

Each repair, however, can entail multiple steps, and that's what I'm using the check-boxes for. Does this still seem like a poor choice for implementation? It seems like having multiple records for a single repair would be a bit of design overkill, but I'm just a beginner.

[Kent Searight]

I don't think it's overkill. I think it's the esaiest way to process the data. Calling out your various line items with checkboxes in a single record may seem easier and less complicated on the surface, but then you'll have to parse the data later to use it. The way I'm suggesting will be less work in the long run.

[mzimmers]

OK...so then, how should the user be guided in selecting from the various repair choices? Should he have to create a new record for *every* type of repair done to a particular card? Or is this where the portals that you alluded to come in?

[Kent Searight]

I'd do something like the attached...

Repairs.zip

[mzimmers]

First, I *really* want to thank you for taking the time to create an instantiation of your suggestion. That is *most* appreciated.

In reviewing this thread, I can see that I did a lousy job of describing the application. For each job, there are a handful of repairs that can be made. These must be pre-defined and have an associated $ value. Furthermore, I have to make sure that each repair is selected only once per job, and I have to allow any permutation of repairs for a given job. That's why check boxes seemed to appropriate.

If I'm going to use the design that you worked up, am I not forcing the user into having to type the repairs in manually? I know how to use a value list for validation, but not for data entry. And what do I do about correlating the list selections into $$?

Thanks a ton...again...

mz

[Kent Searight]

See if this works for you and makes sense. I think that if you can figure out what I've done here and why I've done it, you'll be well on your way to implementing it into your own database.

And believe me...you'll be doing a happy-dance later when you need to use the data for billing, following trends, etc. I agree that the checkbox method is way easier to set up but you will put more effort into working with the data later, especially with subsummary reports (an invoice would be an example).

Repairs_v2.zip

[mzimmers]

Wow...you're incredible, Kent. It's gonna take me some time to digest everything you've got in there. I'll get back, hopefully sometime Tuesday, but I want to wait until I can form at least semi-intelligent questions. Looking at this DB makes me realize how much more I *didn't* know than I thought.

Thanks...

mz

[mzimmers]

OK...a few general questions:

Is the use of repeating fields something to be discouraged? I notice that you have none in this example, and it seems that you've had to go to some trouble to avoid them.

What is the _p and _f convention used for? Is it as in Pater/Filie to denote a "father/son" relationship?

Why would one use the "X" character in the relationship box? When would you want to have ALL records between two tables related?

Thanks to anyone who feels like answering here. Kent's example appears brilliant, but it's so "densely-packed" to this newbie, that I'm having trouble unraveling its mysteries.

[Kent Searight]

Is the use of repeating fields something to be discouraged? I notice that you have none in this example, and it seems that you've had to go to some trouble to avoid them.

Yes, I would discourage the use of repeating fields.

What is the _p and _f convention used for? Is it as in Pater/Filie to denote a "father/son" relationship?

Primary & Foreign. Labeling your keys with suffixes like this isn't necessary, just a matter of personal preference. As I look back over my example I realize I wasn't very consistent in my use of these suffixes, as some keys have them, some don't. Sorry if that caused any confusion!

Why would one use the "X" character in the relationship box?

The "X" you see in the relationship graph is not the same as the "X" comparative operator in relationships, which is for creating a Cartesian join. Double click on the "X" in the relationship graph and you'll see that that relationship is defined by 2 criteria, i.e. repairCostID = repairCostID AND jobID_f :notequal: gJobID.

Hope this helps!

[Søren Dyhr]

Is the use of repeating fields something to be discouraged?

....look before you leap!

Read page 6-7 of this:

http://www.codemastersworkshop.com/Downloads/WhitePaperForFMPNovices.pdf

--sd