Consolidating Tables in File Maker 8 MIT File

Consolidating Tables in File. Maker 8 MIT File. Maker Users Group 3 November 2006 Kevin Cunningham

Why Consolidate? n n n Efficiencies (scripts, etc. ) Allow for consolidated data design (similar tables -> single) Security (single location) Work within Server limits Easier to distribute/maintain

Why Not Consolidate? n n Risk of breaking interface Takes a good deal of time (e. g. , 16 -32 hours for 12 files) Extremely tedious manual work Really need to? (future plans)

Preparing to Consolidate 1 n n n Get File. Maker 8 Pro Advanced Create initial backup Normalize naming: n n n Eliminate unused elements: n n UNIQUE names for all elements Put TABLE identity in names Esp. layouts, but also fields, scripts, etc. Streamline where possible: n n Use new FM functions (Get. Nth. Record) Use modern logic (Case, Else If)

Preparing to Consolidate 2 n n Gather Data on State of Databases n Run DDR on all files n Print PDFs of Relationship Graphs n Get Design data in useful format Standardize an account/password set n n n Reduce hassle of opening files multiple times Make backup copy of revised set May want to obtain Quic. Keys on Mac to automate steps

Preparing to Consolidate 3 n Set order of elements to import n n Decide relationship approach n n E. g. , use anchor-buoy Select File to Use as Core n n decide up front order of layouts, scripts, value lists, tables, etc. Use file with most layouts and/or relationships (minimize handwork) — see the DDR for guidance Prepare Working Directories

Consolidation Steps 1 n Create Value Lists in core file n n Manual (Quic. Keys can automate some) Can supply custom values now Can’t create field-based values yet Create Tables/Fields in core file n n n FM import, cut/paste Issue: core context of newly created tables Issue: if custom functions used, do them first? Option 1: create table names manually, then copy/paste entire field sets Option 2: import whole tables

Consolidation Steps 2 n Create Table Occurrences/Relationships n n Manual (no automation) Create new TOs Link TOs to form relationships Optional: Import Data Now n n n FM import Can do now or later (useful if value lists are based on fields) Can consider imported data as temporary (if users still using dbs) or permanent (if offline)

Consolidation Steps 3 n Repair Field Definitions n n Manual (no automation) Fix defs based on relationships (lookups, calcs) Make sure validation by value lists works Repair Value Lists n n n Manual (no automation) Fix value lists based on field data, related data (Could hold off until VL consolidation phase)

Consolidation Steps 4 n Create Layouts with Appropriate Names n n n Manual (Quic. Keys can automate some) Need to create layouts by name Don’t need to set layout parts/contents May want to create standard dummy layout and duplicate/rename, etc. , via automation (see later layout steps) Import Scripts n n n FM Import Requires fields/layouts name already in place Repair scripts that use literal strings Address scripts for new contexts (can’t assume layout is in “current file”, consider windows) Address “On open” and “On close” scripts

Consolidation Steps 5 n Create Layout Parts n n n Manual (Quic. Keys can automate some) Create proper parts for each layout Suggestion: use vertical view of part names Define part characteristics (e. g. , size, bgcolor) Define layout characteristics (columns, view) Import Layout Elements n n Manual (Quic. Keys can automate some) Prepare original layouts (e. g. , upper-left icon) Copy/paste elements for each layout Clean up pasted layouts (remove icon)

Consolidation Steps 6 n Streamline Scripts n n Manual (no automation) Use parameters, etc. Consolidate same functions into single script Streamline Layout Interface Elements n n n Manual (no automation) Parameterize buttons to use revised scripts Find efficiencies across multiple layouts (e. g. , graphics elements – do you have a SETTINGS table? ) Consider revised navigation schemes Consider standardization of look-and-feel

Consolidation Steps 7 n Streamline Value Lists n n n Manual (no automation) Plan list consolidation Repoint lists on layouts, field defs, etc. Consolidate same lists into single list Eliminate lists from “Other File” (use native value list) Other cleanup n n n Manual (no automation) Repoint file references to current file Consider how to address context in field defs

Consolidation Steps 8 n Implement Security/Access Scheme n n n Manual (no automation) Plan account/privset consolidation Especially consider how to restrict access if previous scheme used “different file” as access control More important to limit database architecture changes (e. g. , have admin password for changes) Other cleanup n n Manual (no automation) Custom function fixes?

Moral n Main Points n Sequence is important (what you do earlier plays out later) n Some steps are more able to be automated than others (some are just manual, it can’t be helped) n No matter how you do it, there is drudgery involved!

Thank You