Lists

[Crackers]

I have a list of occupations that I need to be able to select one of. By selecting the occupation it defines a rate that I use to calculate insurance premiums.

In the past I have put this rate in a global table so that it can be accessed from client files. In the past this rate was manually changed from time to time. (ie: not governed by an occupation selected on a list.)

This time I want the rate to change automatically according to the occupation that is selected.

The list could be in a global table which I can access from the client file to set up the rate, but I really dont want the person accessing the client side of the database to have access to the global list as such.

If I make the list up in a related table - that is related to each client, would it mean the fact it is related to each client going to create large files.

This database will eventually be online.

[bcooney]

I would put the rate in the occupation table. Then, you have a choice. If the client record gets an OccupationID, it can:

1. Lookup the rate using the relationship to Occupation.

2. Simply get its rate "hot" thru the relationship to Occupation.

So, each client record needs an OccupationID. Therefore, build a value list from your Occupations table, ID and OccupationName, showing the second value (the name). Use it on the client form, as a popup menu on the Client::OccupationID field.

PS: Welcome to FM Forums!

[Crackers]

Thanks for your reply, I must confess I had already come to the conclusion it needed to be related. The rate for this I have made into a global field so it can be altered and as I have it unstored when the rate changes the record also updates.

Many thanks for your help.

[bcooney]

"The rate for this I have made into a global field so it can be altered and as I have it unstored when the rate changes the record also updates."

You have a fundamental misunderstanding of global fields. Global fields do not retain their values in multiuser deployments.

[Crackers]

MMMmmmm.

This is what I have done. I have a rate set in a global table. These are in five fields,(global number fields) so I have five rates. Lets make them 1 to 5. I want these rates to be set by one person who controls the rates. From a client file I have a field that has a valued list of five categories that match the five global fields. This is a calculation field that uses "case" to call up whatever rate from the global field the client selects from the valued list. If he selects 4, it goes to the global field 4, and brings back the rate. There are no scripts or calculations that change the master rate in the global field on the global table. I am writing this on a ferry in the Hauraki Gulf (New Zealand)on my way to work. If you tell me it won't work, I am throwing myself overboard.(Not really) Thanks for your comments

[bcooney]

"If you tell me it won't work, I am throwing myself overboard." -- best thread response I've ever gotten!

You don't need this rate in a "global" table. This rate, as you describe, is specific to occupation and so it should be in that table. There's a missing piece here. What kind of transaction record are you creating? I would guess that you wish to grab the current rate for that client by his occupation when you create this transaction, but you do not want the rate to change after you grab it? Correct? Please reread my first response above.

[Crackers]

Still here, haven't abandoned life just yet.If I put the rate field in occupation as a relative table it will only apply to that client whereas the rate has to apply to all clients. For instance say its a wooden building, with no fire protection the rate applying to all wooden buildings with no fire protection may be 1.00%.(Nice round figure.) So everytime a client has a wooden building with no fire protection he will have a rate of $1.00%. During the course of the year the insurer may increase the rate to $1.10%. So the global rate is physically changed to $1.10%. If the client increased his sum insured during the year the additional sum insured would be charged out at the new rate. This is not a conventional insurance deal where the rate is negotiable. This is under what is called a binding rate and the insurer would vary the rates over a period of time according to market forces or their own loss ratios. But that rate would apply to every building that met that criteria. What I have is more than just occupation that varies the rate, I also have construction, fire protection, security and age as considerations. These I have in a global field that the policy table goes to in order to get the various costs that create their rate. Once I have grabbed the rate(s) from the global field I dont mind if the global field changes as the policy premium will change by this rate.I am not creating invoices or monthly premiums from this data base, only a document that will reflect the premium based on the rate that is applicable at that given moment in time. Finally the only person who can change the global rate is the insurer.

[bcooney]

Wow, you just changed all the requirements for a rate calculation. You never mentioned anything but occupation before!

"I put the rate field in occupation as a relative table it will only apply to that client whereas the rate has to apply to all clients." -No, it would apply to any client that is assigned that occupation.

However, now you indicate that a rate is determine by many factors: occupation, item insured, etc. Whatever. You still can't use globals. You can use a table of rate Categories for each combination of item/occupation, etc., then relate that CategoryID.

I feel that I'm missing a lot of info here. Anyway, somehow you must relate a client record to a rate table record.

[Crackers]

Only mentioned occupations as I was trying to keep it simple. It's not practical to have an individual table for each occupation. There are over 300 occupations. Not sure why you say a Global Table is not suitable. It has the advantage of only one rate to change. I have the occupations in a related table - they are actually divided into 6 categories. You can only select one. When you select that one it causes a rate for that category to be plucked from a global table and put into the policy file as the base rate. Selecting the other criteria does the same thing. These rates then are scripted to create the final rate. If the controller changes any of the rates rate in the global fields the whole calculated premium will change. - and that's okay. The global fields are number fields and made global. You have expressed a concern at the global rate changing. But it will only change if someone manually changes it. Scripts only go and get the rate from the field they do not alter or affect the global master rate in anyway. Otherwise the possible combinations of rates are in the millions. Also if the rate is relative to each client, it would be difficult to change a rate that immediatly affected every policy that met the criteria to have that combination of the rate. This way you only have to change the master rates. Unless, as you warn me, globals are not the way to go. I dont want to go overboard again - so say; "Yes Warren, that will work''. Hahahaha, I am on the ferry again. We had Tsunami warnings this morning. I have a suspicion I am not giving you enough information, but this is quite a complicated database and some of it I wrote a year ago and now trying to remember what I did.

Edited September 30, 2009 by Guest

[Vaughan]

You don't want a global table; globals will not behave the way you expect in multi-user mode or when the file is hosted in FM Server. (This is not a bug with global fields, it's the way they work.)

You probably would be better servers with a "preferences" table.Search for it in the forums.

[Crackers]

I am bowing to your greater knowledge guys.

However, I need every person using the database and entering new clients, to have access to these standard rates.

I would have thought Globals were designed for this sort of thing.

So I guess I look closer at Preference Tables and see if I can create there what I need.

[Crackers]

Aha, the penny has dropped. With a Global table and multi users, the last single user to log out will cause the global fields to empty.

Or it could be that they are altered but users may not see the same info.

Thanks for that guys , I will have a look at preference tables and design a work around.

I have used globals like this before but not for multi users so the issue has never come up.

[Crackers]

I have had a look for preference tables but have not found anything concrete.

Can you point me in the right direction?

[bcooney]

"With a Global table and multi users, the last single user to log out will cause the global fields to empty. "

-Not accurate. Globals will initially have the value that they contained when the file was last closed in single-user mode.

"Or it could be that they are altered but users may not see the same info."

-If they are altered when the file is hosted (multi-user), the user that edited the globals sees the changes. Each user has their own "copy" of global field values during their session.

So, a Pref table typically has one record, and contains values that you wish to share among all the users. Also typically, the values in a Pref table are "published" to global fields (or global variables, $$vars) in a script that runs on File Open (a Startup Script). Other methods include specifying global calc fields that equal their corresponding pref field. I use an Evaluate ( ) that is triggered by a field value change in my Pref/Global table.

If during a session, a user (typically an Admin) want to change a Pref field, and broadcast it immediately to all users, he would change the value. Then, each user would have to run a script that "republishes" the globals. Some people have such a script run in their go to Main Menu script.

[Crackers]

Thanks for that, I understand what you are saying, and appreciate your help. Will probably run with your first suggestion.

[Crackers]

Is this going to work without any great hiccup? The maximum number of users at any one time is going to be 6.

I have set a preference table. On here are all the fields I need the administrator to alter. Each of these would only alter probably 3 times per year.

What I need now is to have the corrosponding field in the occupancy table be updated if at any time the field in the pref table is altered.

The preference table is not related to anyone.

What if I create a ''set field'' script attached to each field in the preference table, and this script is triggered to run a script whenever that field is altered.

The script by the use of set field will update the field in the occupancy table.

The script will need to be slightly different for each fields as the scripts are different. (different field names).

Its a bit long winded to write all the different scripts as each field in the pref table needs one.

This is preferable to putting a script on the opening of the file by each individual user which will run each time, but in vain most times, as the fields only change 3 times per year.

[bcooney]

Why not just build a relationship to the Prefs table and use the field directly in your calculation? Since the Prefs table has only 1 record, just use a cartesian join btw the tables.

This assumes that you do not want to capture the values of the prefs and store it unchanged in your transaction. The values are "hot," so to speak, and anything that references them will change when they do.

[Crackers]

Listen Lady, I have spent hours working hard and at very high concentration level to make this as complicated as I can for myself.

Now you have suggested a simple solution.

Come to New Zealand, and I will buy you a good lunch.

Thanks for that. You're a Gem.

Edited October 8, 2009 by Guest

[bcooney]

Glad to help, and that you haven't jumped ship.

[Crackers]

I have created the preference file as you suggested and it works a wonder. This is what I understood Globals were going to do.

Once you master something like this you wonder why it was so hard to start with, and it becomes part of your general knowledge.

The hard part when you help someone else is understanding why they are having so much difficulty with a technique you understand so well.

You are a good teacher. One of things that is difficult to understand in Filemaker is that it will accept things in a simplified form ie: 6 + 5 when logic tells you to use Sum(6 + 5).

To me Globals, (I guess because of their name), seemed designed for what I wanted to do with them.

How wrong I was.

[bcooney]

Well, half the battle is a good attitude, and you have that in spades!

Yes, I can see how the term "globals" can certainly be misinterpreted. Especially since they behave so differently between a hosted file and one used by a single user.

I spent many years teaching applications to adults, and it's good to hear I've still got a bit of the touch.

:thankyou: