Replacing Hard-Coded Parameters
I am updating an app that allows EOTI users to add records (submit permit applications) to a table (Permit Applications) .
There is a formula-number [Amount Due] field derived from several [*Category*] field values. The logic includes 40-50 hard-coded values (fees and expiration dates for pro-rating). The fees and dates will be changing in the near future. The payments are processed externally and not connected to QB. However, existing QB records will report an incorrect Amount Due when the hard-coded fees/dates change and be inconsistent with the external Payment system.
I am considering 3 solutions to replace the hard-coded logic, but I am looking for feedback and/or alternate suggestions.
- Update the existing table. Create a new field for each of the 40-50 parameters and replace the hard-coded logic with the related fields. Use Default Values to initialize values for new records. Going forward, update the Default Value when fees/dates change.
- Create a new table (Parameters).
- Structure Options:
- Each parameter is a separate field. A single record supports all 40-50 parameters. A single relationship to the parent Parameters table supports 40-50 pairs of lookup/snapshot fields in the existing child table. The snapshot fields support a point-in-time value, so fees/dates may be updated when required. Alternately, the Parameters table could include a new record when any parameter changes and the snapshot fields would not be necessary.
~OR~ - Each parameter is a new record with [Category], [Param Name], and [Param Value] field values. The Permit Applications table includes a formula [Related Parameter] field (similar logic to existing [Amount Due] field, but identifies the parameter instead of hard-coded values) to support one pair of lookup/snapshot fields with the permit amount. Like the other table structure, this design could either be limited to a single record for each parameter or new records when values change.
- Use a Pipeline to update an editable [Fee Amount] field (from a Parameters table) when records are added.
Note: I prefer non-pipeline solutions because I am one of several staff supporting almost 200 apps, so updating/reviewing pipelines requires me to logout of my user account and login with our shared/service account. Then I have to identify the correct pipeline from a continually increasing population.
------------------------------
Erich
------------------------------
Snapshots seem like the way to go. Normally I would suggest a master table of the 40-50 parameters but you have an unusual situation here where you don't seem to be adding child records to your master record. If this were say a typical billing situation where you had a parts master with prices and an order and you were creating order lines than the typical set up here would be to add an order line choose the part number into the join table and look up the current price and then have the corresponding snapshot field.
But it sounds to me like you don't want to have a child records on your form so I guess it does make sense to have one master table with all of the various permit fees, in one record. Those would all be looked up, all 40 of them as look up fields into your data entry record and then each one would have a corresponding snapshot field.
------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------
