I will need to save general settings for my application ie: NextInvoiceNo, NextPONumber, SalesTax, etc. They would need to be accessed from many different places in the application.

Should i store them in its own table (ex: settings) in one record only - use global or regular variables ? What is the recommended way of doing this ?

The application will be accessed in a multi-user environment (3 PC,s - 1 Mac - No dedicated FM server). Does that have an impact if I use global variables ?

Thanks

I store mine via the 1 table 1 record method as I think this is the most stable.

Access to the data from anywhere is made easy by the use of cartesian ('x' type) relationships where all records match all records

HTH

Phil

Hey Normand!

Phil's got the right idea. (Dang it! He often posts before I can compose a reply. Must be that British economy of language. }:| )

I believe you'll have greater reliability if you go with a table. Fields such as Sales Tax are great candidates for a "settings" or "prefs" table. This allows all users to look to a single location for data.

This may be doubly important when it comes to your NextInvoiceNo and NextPONumber fields. (Sounds like you're implementing a custom numbering scheme.) Variables would probably produce undesired results. First, since global variables are unique to a user's session and "sequestered" from other users, each user could be posting his/her own NextInvoiceNo ... that's probably not what you're wanting. So, you would want all users to look to a central field. Second, global variables exist only for the duration of a user's session. Therefore, you would have to "establish" them at log-in, and your script would have to look "somewhere" ... it may as well be a central field.

If I haven't misunderstood what you're talking about, you'll also have a better chance of success if these types of fields are non-global. This, in turn, will require that you add table occurrences/relationships where needed.

Hope you find this helpful.

Thanks both for the comments. Sorry, I mixed "global variables" with "global storage fields" - getting there.... Thanks for the clarification.

So if I understand this well:

1) Should put my setting fields in a Table, in 1 record, as regular storage variable (not global).

2) Define a cartesian relationship (I will try to read more on this after this post) between the Settings table and each of the tables where I might need to have access to the settings fields. (}:|)

This leads me to another question (if you don't mind): I'm worried about the part where user A is about to post a new invoice and update the NextInvoiceNo, while in the mean time the NextInvoiceNo field was updated by User B on another pc, ending up with the NextInvoiceNo not properly updated. Does FM do automatic locking of this Settings record while a user is working with it, or I will have to check this manually before saving (ex: re-read NextInvoiceNo just before the commit)

Many thanks.

Beware of using shared fields like this. If different users happen to be updating the PO Number (or whatever) at the same time, you could end up with errors or duplicate PO numbers. It may not happen with so few users, but you should still error trap if you plan to use this technique.

Ok. will do.

I'm curious about what would have been the effects of defining the fields as global storage instead of normal storage in this case. What kind of problems it would have created ?

Setting any field to global storage means that not only does that field have one value for all records but also that each user can have their own value in any one global.

This is an extremely flexible tool to use in any solution but is one to be very careful of in a shared environment.

There are a number of other qualities where a global differs from a regular field but those are the most obvious two.

I recommend you look up global storage in your help files and get your head round them. They are as invaluable when used correctly as they are dangerous when used incorrectly

HTH

Phil

Hi Ender,

I have seen you and others caution against this practise before but I have not yet come across a simple (to me anyway) explanation of how else to reliably generate numbers such as next invoice number or next PO number and so have stuck with this method.

I have seen it mentioned that the only really reliable method of generating sequential numbers is to use FM's own auto entry but I have yet to work out how to apply this in my solution.

Is there a simple way or am I best, given my limited power of understanding! }:| , sticking to the way I know ?

Phil

I also agree with Ender's admonition against such schemes. I couldn't decide in my first post whether to warn/discourage Normand about it. Upon reflection, I should have said something about the issue.

I have seen you and others caution against this practise before but I have not yet come across a simple (to me anyway) explanation of how else to reliably generate numbers such as next invoice number or next PO number ... I have seen it mentioned that the only really reliable method of generating sequential numbers is to use FM's own auto entry....

I've had success with numbering schemes that are generated partly by the Serial ID. For example, an Invoice number that concatenates (a) the Customer Number, (}:| the last 2 digits of the current Year, © the 2 digits of the current Month, and (d) the last 4 digits of the Serial ID ... "12345-0610-4444" or similarly. If we use a "fixed" length for each of these parts, and our calculation results in a Number, this scheme also delivers on the Sorting side of things.

However, development for our own consumption is easier than that for clients. When we're "rolling our own," we can decide on almost any numbering scheme that satisfies our needs. When building for paying clients, though, we're often presented with the scheme they want. Sometimes it dovetails nicely, but it usually requires a meeting ... and a follow-up meeting, then another meeting....

how else to reliably generate numbers such as next invoice number

The only reliable way I know of to generate UNIQUE IDs is by auto-entered serial numbers. I am not sure what use is there for the NEXT invoice number, but it is available thru the GetNextSerialValue() function. Of course, it's quite possible for another user to create a new record AFTER GetNextSerialValue() has been evaluated. That's why the result cannot be trusted to predict the next actual ID.

Any other scheme that relies on the numbers not likely to repeat is playing the lottery. It's not likely for any individual record to win. But given enough records over enough time there eventually will be "winners".

The need for NextInvoiceNo: In a "Subscription" table, the user would press on an "Invoice" button to indicate that this subscription can now be invoiced, which would assign the next available invoice number to this record, flag it as having been invoiced, and then print an invoice for it.

I guess I should try to see if this NextInvoiceNo could be somehow auto-entered, and maybe even stored in the Subscription table instead of in the Settings table maybe ?

Question: can a regular field be defined as auto-enter with a sequential number, but be only assigned when requested, instead of on record creation ?

the user would press on an "Invoice" button to indicate that this subscription can now be invoiced, which would assign the next available invoice number to this record, flag it as having been invoiced

I believe the way to set this up is by a relationship between Subscriptions and Invoices, based on SubscriptionID. The relationship is allowing creation of new records in Invoices.

Pressing an "Invoice.." button in Subscriptions runs a script that starts with:

Set Field [invoices::SubscriptionID ; Subscriptions::SubscriptionID ]

This generates a new Invoice that is already assigned uniquely to the calling Subscription. Calling the same script again from the same record in Subscriptions does virtually nothing. A Subscription is flagged as "Invoiced" by virtue of having a related record in Invoices.

Sounds very good, and looks much safer too. So I will remove the invoicing information from the subscription table and put it in a related invoice table using SubscId and InvoiceId.

Thanks very much again !

It doesn't matter much in which table the actual invoicing data is. If you prefer, the Invoices table could be no more than a numerator, with only the two fields InvoiceID and SubscriptionID.