I've got a Filemaker database that has many checkbox fields, and use them as a way to identify the background info for each person in this database.
For instance, I use some checkboxes to identify someone's specialty (e.g., architect, plumber, electrician), and for another set I use checkboxes for projects they're associated with (e.g., apartment construction, house demolition, etc.).
I'd like to archive several of the checkboxes -- the checkboxes themselves, and their corresponding values (i.e., if they're checked) -- where I'd like to archive them, and write new checkboxes in their place.
It's been a while since I've set up a Filemaker database, and I've never archived fields / values. I know this is a broad ask, but I'm seeking guidance on how to go about doing this. Happy to provide further information as I go.
You know the checkbox options are derived from value lists. You can create new value lists (keep the old ones) and attach them to the fields.
Archiving in this case could be very simple. Before you start, ensure that your table has an ID field. You need to have unique IDs for each record.
I'd suggest that you copy the table which contains the fields with the data you want to archive. Do this in the Database Manager dialog. Select the table, Copy, then immediately Paste. It will paste an empty (no records) copy of the table. Rename the new table. From the new copy of the table, remove all the fields except the ID field and the fields you want to archive.
Switch into the relationship graph. Create a relationship between the original table and the new table, set the relationship to ID = ID. Save changes and close the database manager.
Make a layout based on the new table which is going to be your archive.
Now go to a layout of the table you want to archive. Show all records.
Switch back to the new layout and choose File > Import ... Locate the current database using the file browser and Import ( matching on names ) the table you want to archive into the new table.
Now you have a copy of the data stored in a related table. You can display the original data (using the original value lists) beside the new data. Or you can use a button to access the archived data via Go to Related Record().
Checkboxes are difficult to work with. In the long run, I would switch to a portal where you enter the attributes. This way, the data is already stored in records, allowing you to process it further.
There are a couple of techniques for dealing with checkboxes.
Note that a field based on a checkbox, with a value list associated, stores carriage return delimited checked entries IN THE ORDER CHECKED.
If attempting to do any statistical analysis on the checkbox entries, this conglomeration of text in a list is almost impossible to extract meaningful data.
In the past (deprecated) I'd create a field for each checkbox, but that has it's own share of limitations and dev issues.
More recently, I have that one field with the value list and checkboxes, AND a set of fields that are auto-enter pattern counts of the individual check box items. In that way, I can change the value list (additions would need another auto-enter field), but subtractions require no changes.
And the individual field by checkbox item, allows things like COUNTs and other statistical compilations of value.
Bonus point (from Six Fried Rice blog),
GetValue ( self ; ValueCount (self )) as an auto-enter calc, forces the LAST checked entry to be the only one stored. You can change the ValueCount to a number to get that limited number of responses, or to 1 to only get the first checked item.
Also of note relative to the above calculation: There is a long-standing (like forever) bug in FileMaker that makes a radio button field - which by convention is a mutually exclusive selection of the last item checked - multi-selectable. If you hold down the shift key, you can select more than on radio button - not kewl. But the GetValue ( Self ; ValueCount (self)) auto-enter is a workaround to that bug.
Thank you so much, @Malcolm -- this is incredible advice, and I greatly appreciate all of the clear steps you've provided.
One quick question before I get started: Is there a particular table / feature (might not be the right term) you'd suggest for archiving the old checkboxes and their respective values?
You mentioned how I can "use a button to access the archived data," and I'm wondering if that's the suggested solution -- or if there's another "feature" (e.g., some kind of drop down menu or something) that I should consider for the archival checkboxes and values.
Ok, I've got checkboxes for a section of my database named "Background Info," which is actually divided into four parts (columned sections): A , B , C , D. Columns A & B are macro categories; columns C & D are populated with more granular info.
I've also got another small area for checkboxes, named "Type of Project," for my current / ongoing projects. Like I said, part of the reason why I'm making these changes is because I'm no longer working on some of those projects; they're outdated so I want to archive them (while retaining their values for people associated with those projects), and create new ones. Ditto that for columns B, C & D in my Background Info section.
When I go into Layout Mode, and click on the boxes for these sections, the Control style shows they're "Checkbox Set."
Would it be possible for these boxes to both contain checkboxes and either a Drop-down List or Pop-up Menu to store archived checkboxes and their respective values?
BTW, I'm happy to consider other control styles -- whatever others thing would be a better fit. But the main goal is to converted outdated checkboxes and their respective values into another (more compact, collapsable) control style, hopefully within the same "box" (section, area) or just beneath it.
If it's not possible for the existing boxes to contain "Checkbox Set" and a Drop-down List or Pop-up Menu, I'm guessing that I'd have to readjust the existing boxes, and creating new ones underneath them to contain a Drop-down List or Pop-up Menu. Is that right?
If you follow my advice to copy the old info into a table, and then create a relationship based on ID = ID, this will form a 1 to 1 relationship, there will be a direct and unique link between the data in the current record and the data in the archive record.
I would create a button with a popover, and place the checkbox field from the archive onto the popover. The popover button could have the info icon without any label so that it doesn't take up space. Place it beside the existing field and it will allow you to look at the older info at any time.
Thanks for your latest reply. I've started following the steps you suggested, and just wanted to clarify what I'm trying to do to ensure it works with your instructions.
To be clear, I want to archive just certain checkboxes and their respective values for three sections: two background info sections (populated with granular info about each contact), and the "Type of Project" section. I do not want to archive the contacts themselves; many would remain dynamic and very much part of the database.
I'm raising this because:
(1) I'm wondering if that changes your instructions, i.e., if it's possible to create new tables, set up the relationships between them, then set up the button function you suggested for accessing the old checkboxes / values, and then edit the checkboxes in the background info and "Type of Project" section; and...
(2) Regardless of which process I follow -- your first instructions or some variation of what I just wrote -- I was also think that I'd create three discrete "checkbox archives" for the three checkboxes / values I mentioned. IOW, copy the table three times in Database Manager, name the new table after each discrete "checkbox archive" section, and proceed from there with your remaining instructions.
Again, I wanted to clarify my aims, and understand what direction you'd take based on what I've described. Thank you -- again!
Ok, thanks. So, just to be sure that I’m following you correctly, after copying and creating new tables — from the original tables that contain the checkboxes — I’d continue following the steps you outlined in your first post, namely:
Is that correct?
Moving on…
I appreciate your input, and I’m open to trying other approaches. I was thinking I’d have three discrete “checkbox archives” — using the button with a popover — for each of the two granular, background info sections (which are in separate columns), and the “Type of Project” section (which is located in a separate area).
I thought it might be better (and cleaner) to separate the archived sections in their respective areas, rather than collecting two sets of background info and “Type of Project” in one central location.
Again, happy to consider other, suggested approaches!
Yes, you're correct in the first question. And for the second, if you want to do it your way, it will work. I don't see any advantage to doing that but if you have a vision for doing it that way then go ahead.
Great. Thanks! Just wanted to double check before putting things in motion. I'll work on this now, and report back.
Thank you. Honestly, I don't have a vision, per se -- just seemed liked a way to organize archived checkboxes, by segmenting datasets. Were you imagining such archives would be in one unified area?
Again, I'm not tethered to any particular idea, and welcome ideas / suggestions on how to put this together. It's been eons since I've designed a database in Filemaker, so I expect there are newer, better ways of doing things I'm unaware of...
Thank you again, @Malcolm -- I greatly appreciate your help!
Ok, I've followed all of your steps, but...I'm getting stuck on creating a "a layout based on the new table which is going to be your archive." Here's what you wrote:
Here are the steps I took...
I went to File > Create New... and selected "Blank" (though I could've selected Contact Management or Contacts)
After naming that new file, I went to File > Manage > Database...
Within the Tables window, I selected Import...
Then navigated to the my original Filemaker file, clicked Open
And then, in the Import Tables dialogue box, I selected the table I created for the archive data set
While I imported the table successfully, it shows the number of fields -- but also show "0 records"
Is this right? Somehow it feels like I'm overlooking a crucial step or misunderstanding how to put this phase of the process together.
Thanks, @Malcolm Appreciate your quick reply. I'm still not understanding some parts of your instruction. Let me quickly clarify...
Yes.
I've done all of this successfully. However, Filemaker isn't permitting me to delete certain fields. Still, the ID and most important fields are there.
Done.
Ok, here's where I think I might be getting tripped up. As I said in my last post, as I understand creating a new layout for the new table involves: Going to File > Create New... and selected "Blank" Is that right?
Moving on...
I tried this but after I select File > Import Records... and navigate to the file and select the table that contains the data I want to archive, under "TARGET" within the Specify Import Order window, I'm unable to select the newly created table -- it just reverts to default table for the newly-created layout.
I'm still not clear about what this new layout does and how it interfaces with the original file, but I'll keep ploughing through. As I said, I'm just tried to archive certain checkboxes; not the records (contacts) associated with them. Just to be clear...
Sorry if I'm misunderstanding certain instructions... Thanks again for all of your help.
Sometimes you'll get a warning saying that a field can't be deleted. This is because another field relies on it for a calculation or a validation. If you delete the field that relies on it first you can then delete the field.
You want to start with View > Layout Mode. Then Layouts > New Layout/Report.
The import always tries to target the table for the active layout. You need to be on the correct layout, then start your import.
Got it. I tried it, but seemed to just get a blank layout -- and had difficulty getting out of that layout, and into the regular database / Browse mode.
Should I be doing something else, like within the "New Layout/Report" section, be selecting "Computer" and then something like "List"? Or do something else?
By being on the "correct layout" you mean the newly-created layout, correct?
Thanks again for your help, @Malcolm. Hope I'm getting there!
I've followed your steps for the View > Layout Mode, then Layouts > New Layout/Report -- and then selected "Computer" and "List."
Also, when I select File > Import Records... I originally tried to navigate to one of my newly-created tables, to under "SOURCE" for archived checklists, but got an alert / dialogue box saying: "A table cannot be imported into itself."
So, instead, I chose the original Table (from which my newly-created tables were based), and hit Import. That seemed to work.
Once done, I see blank rows in the Layout mode.
I'm unsure of the following:
How getting out of that layout, and into the regular database / Browse mode;
If there's a way to format this layout, so that I can create a button with a popover. Or does that happen in when I navigate to the regular database / Layout mode, and do it there?
It is normal for the Layout to be empty. First you have to set the Table the Layout will display the data from. Once set, you will have access to the Fields from that layout, Fields you can drop on your Layout. If the Table is set on a Table Occurrence (TO), and that table is related to another one on the Relationship Graph, then you may also display the related fields on the Layout.