Calculations, Case, Result Storage

[James22]

I have built an app for my client with several layouts/forms. I'm introducing FM to a large firm. I have to make this app sizzle. There are many doubters of FM that I have to convert. Guess what, they love MS tools; nothing else exists for them.

The app will run on Win XP and eventually will be multi-user and web. For now, one desktop.

Now I need to do some reports. The main report has to make calculations based on the selections made in the input forms, almost exclusively via many radio buttons for various aspects.

In general the app allows someone to observe and assess and then record their findings during & after their inspection.

I am trying to figure out a conditional test of data to get a value that I would assign in a calculation.

Example;

Let's assume that we are looking at a car and the various parts or aspects of the used car to determine how much it will cost to repair within the warranty period.

The goal is to add up the cost for doing the brake work in 3 cars being inspected. I will actually do this for 200 cars and the report will add up the costs.

But for this discussion let's assume 3. When I inspect the first 3 cars, I notated in the field if the car needed no brake work, minor repairs, or major repairs. So let's assume an estimate of $200 for minor and $500 for major.

I will have a script test if the answer in the field for record 1 is none, minor, major. Let's assume minor. So I need to assign $200 to this record and store it somewhere. Let's assume record 2 is $0 and record 3 is $500. So the total would be $700 for the 3 records.

The same approach would be used for engine, upholstery, paint, etc.

For this exercise I think the best route is to use the 'case' function. Should I test for the exact phrase selected in the radio button value set on the form for that record or should I test for a non-null in that field? I think if the script checks each of the 6 choices in sequence for the non-null it will work. Correct?

I think I need to store the results. This would make it easier for me to troubleshoot my reports. I could export the file and do the calcs in Excel and then compare the results to my data base report. Does this approach make sense to you?

I am trying to figure out the syntax, figure out how to assign the correct value; how to store the result if I'm going to. Then I have to figure out how to make a report work.

One book said to do a find which seems to be a simple query. It said to find all the records that meet one criteria then run one script. Then do the next one. I can see the logic but this appears to make more sense if you are just getting a total for the pile of records, within one field.

I will have several different values that fit the different value sets for the many radio buttons [probably a total of 80 to 100 button sets but a total of 15 value sets. The calculations will use 25 or so constants [the $200 value above as an example]. I want to have these stored in a table with a layout for maintenance of the constants. Maybe the $200 value is good today but next month it has to be changed to $210. I certainly don't want to have to change scripts nor should the change need an IT person to implement it.

A screen or layout is needed so a non-IT person can change the constants at will.

I read that grabbing values from a related table prevents FM8.5 from storing the calculated result. If so, I think I'm screwed.

Can anyone make some suggestions and tell me if I'm going down the wrong path?

Cheers, James22

[jteich]

Are you sure that your data model is suitable for the reporting you want to achieve?

The better the data model, the easier it is to get the data summarized!

-jens

[James22]

You have a very good point. I do not know yet. My first problem is more basic. I cannot get a basic case function to work.

The use of this function is new for me. If I have a field named "car paint" in table called "review"; and the value set for the radio button for this field was: good, minor repair, major repair; and respectively I want to substitute these values for the value set: $0, $200, $500; how would you write the statement?

I have been looking through 4 books and the online help, etc. and have yet to find clear descriptions of how to do this. Problem I think is that the text is aimed at experts who would be the ones using this function.

Can you help me?

Thanks, James22

[jteich]

You have a very good point. I do not know yet. My first problem is more basic.

There is nothing more basic than construction of the suitable data model!

I want to substitute ... good, minor repair, major repair;... for the value set: $0, $200, $500;

see this

-jens

[IdealData]

Whilst the reference to the CASE function is all well and good it will probably help you to understand with an explicit example:

CostOfRepair=

Case(

CurrentCondition = "Good"; 0;

CurrentCondition = "Minor Repair"; 200;

CurrentCondition = "Major Repair"; 500;

0)

Where:

CostOfRepair is the calculation field holding the repair cost

CurrentCondition is the check box field

I have written the calculation in a style I use to make things more readable (FM is quite happy with extra returns etc.) and it should be obvious that the calculation executes in the sequence shown (first comes first). Each line is a question with an answer - the last line does not have a question but provides a default answer only. In my example I have used fixed values, however you can easily substitute these for fields stored in a table. It's nothing more than basic algebra.

Are you sure you have the skills to introduce FM at this level? Calculations are the real fodder of FM and you will need to know them in depth to produce a quality system, either for in house use or commercial possibilities. No offence intended, just a "heads up".

HTH

[LaRetta]

Optimization is important as well. Since vs. 7, some * calculations short-circuit (they stop as soon as they hit the first true). So which value will the majority of records fall under? If 40% will need minor repairs, 30% will need major and the rest 30% are good (or undetermined), then write your 40% chunk first. This is called branch prediction and it can save several seconds in evaluation time ... every nano second counts. Many times, the Developer or Owner can predict which are the highest percentage record-groups. For instance in Invoices, the majority are paid (and so on). In the prior calc example, an evaluation on Good isn't needed because it can be grouped with the default result of 0.

CostOfRepair=

Case(

CurrentCondition = "Minor Repair"; 200;

CurrentCondition = "Major Repair"; 500;

0 )

The zero isn't required either but some businesses like it displayed if there is no dollar value. If you don't want the 0 then replace ;0 with nothing. If no default is specified, FM assumes null on both If() and Case().

* If(), Case(), AND, OR, NOT

LaRetta :wink2:

[Fitch]

Now you know how to write a Case() function. The question is, is that the solution to your problem? From your original description, I would say no. The Case() method will require a developer (you) to make changes any time the prices change.

What you probably want is a table that lists all the prices. I.e., each record would contain a price and description and possibly a code or ID to identify it. Then, instead of using Case(), simply set the price field to do a Lookup based on the description (or ID).

[James22]

Tom;

You're 100% correct. This is the last part of my original message:

****************************************

will have several different values that fit the different value sets for the many radio buttons [probably a total of 80 to 100 button sets but a total of 15 value sets. The calculations will use 25 or so constants [the $200 value above as an example]. I want to have these stored in a table with a layout for maintenance of the constants. Maybe the $200 value is good today but next month it has to be changed to $210. I certainly don't want to have to change scripts nor should the change need an IT person to implement it.

A screen or layout is needed so a non-IT person can change the constants at will.

I read that grabbing values from a related table prevents FM8.5 from storing the calculated result. If so, I think I'm screwed.

*******************************************

I will now try what you suggested. My email address is [email protected]; please email me when you have time.

Cheers, James22

[LaRetta]

I agree, Tom. I saw 3 value list items and 3 prices in a Case(). The prices could be global (or Preference) fields. But then ... I didn't read the entire thread either. I spotted a Case() calc and responded.

That was sloppy of me. Thanks for steering it back on the proper path. :wink2:

[James22]

I am totally amazed. The FM experts speak a language to themselves which is a reflection of the power and the variety of things one can do with this desktop powerhouse.

I would be greatly appreciative if you could take your suggestion from above, which is:

Case(

CurrentCondition = "Minor Repair"; 200;

CurrentCondition = "Major Repair"; 500;

0 )

and then change it to show the use of a global field. If I understand the global field, it can be used to set the price for a product or a tax for various calculations. In my case, I would assign a value as a global variable, for each type of repair.

I use the same value set for different rooms. The simple situation is that the global var for the value, "Repair - Min", is the same value for each room calling it up.

What if the value for "Repair - Min" for a Bathroom is $250 but for a living room, it is $400?

Do I set up a table with the global variables for easy maintenance on a form, by non-developers? This is what I want but I remain clueless as to how to make this work.

I really do need to take some courses after I get this app done.

Thanks, again, James

[Fitch]

I read that grabbing values from a related table prevents FM8.5 from storing the calculated result.

Calculated fields based on related values cannot be stored, true. But you can lookup data from related fields and store the result in a plain old non-calculated field. I believe this is the solution you are looking for.