In this database I need to keep track of what is the name and end date of the currently active semester. It's the same for all users and needs to be remembered. I also keep track of when imports last happened and such.
What I do now is I have a "Settings" table with a field called "Number1" which is just the number one. I then join that to my login screen like this "Settings_Number1 ≠ JoinTable_PrimaryKey".
On startup I turn these into variables which I use to filter portal rows and run SQL commands and such.
It feels messy, is there a smarter/more standard way?
Is there a better way?
Why do you think it's messy ? Unfortunately FileMaker does not have constants built-in, that would be so useful. Copying the settings to variables has a draw back, by mistake a script step could overwrite them. A table with read-only access is one way to simulate read-only values.
We tend to do something similar, mostly by uploading to global fields rather than global variables, with a couple of exceptions where we use variables.
However, we tend to only use this method if we are needing to control something in real time, such as the menus that appear within custom menus, hiding objects, etc.
If we need to refer to a setting on demand, such as seeking the latest tax rate, then we use ExecuteSQL. A dedicated settings table tends to have a single record for the system to work, hence only SELECT and FROM needs to be used. Tables containing multiple records, such as a list of currency rates, also tend to be low in numbers, hence this approach is still quick, but WHERE now needs to be included.
These 2 approaches avoids the need for additional table occurrences such as Tax to Quotes, Tax to Orders, etc. thereby reducing the structure and overheads on the solution.
Regards
Andy
1 Like
Having records store settings as data is a pretty good way of doing it.
If you need "constants" then custom functions work well and can be flexible.
Also, value lists can store information and never need to be exposed to the user. Unlike records, value lists are never out of context. Also, you can create value lists from data stored in records. So there is a way to get the benefits of both value lists and record data.
1 Like
I think it feels messy because we don't have constants and can't join based on variables, so we're making these bogus fields and manually populating them and it just feels clunky and error prone... but it's not horrible. I just wanted to make sure there wasn't a "use constants" option I was missing.
For sure when we have to be creative to get what we needs it may sometimes be clumsy, others though suggested very interesting solutions.
As mentioned in the other thread about arachnophobia, I use a Globals table (one or more) for certain utility data, a Settings table for typically constant or semi-constant data that is used broadly across a system, AND I need several "History" tables that get a newly created record to record a transaction event which I'll need to refer back to for reporting, audits or security, etc.
For example, one such table is ItemLocationHistory which gets a new record each time a controlled item is transferred to another analyst/scientist. Things like timestamp, who transferred it, to whom they transferred it, reason for transfer, IP address of sender and recipient, persistent ID of sender and recipient, pending receipt flag, etc. The movement history is viewable on each lab record at any time (with a subset of field data).
Another is a very simple history table which simply logs the total number of specimens in-house at the end of business every day. It's always changing throughout the day and can be seen in real-time, but I need a historical record of the end-of-day number of jobs and specific specimens count (often more than one per job).
2 Likes