Advice request for use of tables

[lorivol]

I have a database that keeps growing larger and larger (the number of fields, not the number of records).

I want to reorganize before things get out of hand, but I don't know if I should create related tables for different categories of information or if there is a better solution.

The main content of my records comes from an outside source, which sends me an excel sheet with applicants for the program where I work. This is all demographic information and contact information.

Next, I begin analyzing applicants, and marking the applicants in various ways, creating new fields based on their qualities, status, etc.

The issue is that it is becoming increasingly difficult to import the excel sheet when I receive it (on a weekly basis), because of all the fields I've added. I also have to admit that I haven't quite mastered the recurring import function, yet, but mainly because the person who sends me this file never uses the same headers, so I've been afraid to try this.

Basically, it takes way too long for me to match up these records every time I want to import new records, and update the existing records, and I was thinking that I could have only the items on the excel sheet in the main table, and then have all my added fields in related tables matched by an auto-created serial number.

Additionally, this would make it easier to locate fields if they are categorized in separate tables based on their specific use.

I suppose the question is: Is it stupid to make related tables for the sake of field organization? If so, how can I better organize these fields to prepare for import?

[LaRetta]

Ideally, you should require the data be properly formatted using a standard template BEFORE import so that each row is a record, and the field names remain consistent or always in same order so the process can be mapped within the import script. The Developer should not be responsible for the data being correct nor responsible in selecting which fields match (according to mismatched field names). If the fields are out of order, it is tedious and time-consuming and it opens up additional possibility of error.

But if you can't ...

If the person giving you the spreadsheet uses different field names they might also have fields out of order so you cannot script an import based upon matching names or creation order. I do not see how a manual import can be avoided so I suggest that you import manually into a separate TEMP table in same file (which holds your proper field names). From there, you can solidify a scripted import map to your main table only continuing after you have scrutinized the import results.

There is nothing wrong with having 1:1 relationship. Two reasons why you may want to split off some fields, 1) you have several large text fields or 2) you have many 'unique' fields, such as completing a government CPMS form and its fields are unique to that form. Until FM comes out with folders for fields (or allows true separation of data from schema), it will always be a balancing act for the Developer. Two reasons NOT to split fields would be 1) the field being considered may be used in a join for filtering or may need to be indexed or 2) it is an attempt to correct an incomplete structure or solve a problem which might more easily be solved elsewhere.

I don't know if I should create related tables for different categories of information or if there is a better solution.

I could not make suggestions on how the data should be organized without more information, preferably a zipped file but even relational graph screen shot would help. There is not hard-fast rule on good structure (it would vary by business rules) but there are some obvious things we might be able to point out.

[lorivol]

I truly appreciate your thoughts.

As you'll be able to tell from the zip file, I work at a university, so I had to remove all the records from the database for privacy reasons. I also deleted all the report layouts, mostly because there are a ton of them and some are used, some are not, and I thought it would be easier to not confuse things with layouts that might be outdated. You have to log in using "guest."

The layout is the main layout I use for each student, and you can see the number of fields and the tables I have so far.

You might ask why some tables are related by student ID and some by applicant ID, which is an auto-enter field. There is no answer to that except that this is a definite work in progress. I also don't know much about the join relationships you discussed, so I'm uncertain as to whether or not I may need to use them.

The other issue I face with fields in separate tables is that often I import a new group of students. Then, based on their qualifications, I might want to mark them all with a certain status or use some other field that describes them all. If that field in the same table as the rest of the student's info, I can simple do a replace field contents. However, if it is in a separate table, is there an easy way to create a new record in advance so that the replace field contents would work on a related field? For example if table 1 is the main table where lists are imported and the auto serial number is created, could the record and matching serial number be simultaneously created in all the related tables so that if the field from a related table is on a layout, it would act the same way as if they were in the same table? ( I know that is probably really confusing, but I am not sure how to explain it better).

Again, thanks for your help!

Example.fp7.zip

[LaRetta]

As Guest, we cannot see your tables or relationships ... things we would need to see to make suggestions. :)

[lorivol]

Well, I guess that would help out if you could see it

Attached is the file. Use the login name Admin2, with no password.

Thanks!

Example Admin2.fp7.zip

[LaRetta]

I see no obvious issues (except the things you already mentioned). 174 fields isn’t that much. I would question why you have 5 Reviewer tables instead of one. In many of your tables, dates are of type Text and many tables still lack a unique ID (all tables should have one). These are things I’m sure you are addressing.

I might suggest that you modify your data so it produces 1 for ‘yes’ and 0 for ‘no’. If the data imports, you can convert it within your import script. This would eliminate 10 or so calculations. With your extensive analysis, I’m not sure how else you might fine tune although I’m not sure you are taking full advantage of table occurrences (Reviewers, for example can be one table but five occurrences of same table). I also suggest that your 2010 Applicants table should be simply Applicants with year field (year can also be populated during import script).

As for splitting some fields to another 1:1 table, I don't believe that easing the burden of import is a good reason (with what I've seen and understood ... please read my signature line).

… it takes way too long for me to match up these records every time I want to import new records, and update the existing records

Because of potential fieldname/column order problems during import (not to mention potential blank lines), I would not risk an import directly into your Applicants table. Create your temp table by importing your Excel data and specifying 'new table' on the target side. At the least, import into it manually (having fewer fields to scroll through as you requested). At best, the person will guarantee using those same field names so you can map 'by field name' into Temp and the entire process can be mapped, including the import from Temp to Applicants.

Still keep it in two scripts ... the import from Excel - (then manual quick review by person looking for oddities or blank lines) - then moving to Applicants. In this way, you can make sure the data is correct before true import. It is commendable that you have presented your file along with informative and concise presentation. I hope others offer their input as well.

[lorivol]

LaRetta, I appreciate this input so much! (I also appreciate your signature line!).

I am totally self-taught on filemaker, and while I do have some things I'm really proud of figuring out, there are still many things I don't have a complete grasp on. If you have time could you elaborate more on a couple of your recommendations?

1. Regarding the Reviewer tables... are you saying that I could have multiple reviewers but use the same table? If I did that, would I have to make a field with the reviewer's name? (Not all the reviewers use fm, so some of them fill out their reviews of applicants in excel and I import it for them, so it couldn't be an autoupdate field by user).

2. I've been looking for more information on the "1" and "0" issue, as well. This might seem really basic, but if the other people using the database (we have it on fm server), need to be able to click "yes" or "no" to answer certain questions, like whether or not a student returned their contract. I don't think it would be best to instruct them to type in 1 or 0, but is there a way that they could enter the text and then it is converted in the same field to 1/0 for calculations, but still reads "yes"/"no" when you look at it on the layout?

3. I like the idea of creating a temp file, because I think it would help out with the 1:1 issue as well as the field names. If I had a temp file with ONLY the fields that are on the excel spreadsheets I receive, then I could import the spreadsheets much easier and then have the import script to take that into the overall database. Another thing that is pretty basic, I'm sure, but I haven't tried an import script before. I'm not sure I understand where I'd put that or how to write it. Do you know of any reliable tutorial on import scripts, specifically?

Thanks again, I hope you have a good weekend!

[LaRetta]

1. Regarding the Reviewer tables... are you saying that I could have multiple reviewers but use the same table?

Yes. You have five identical Reviewers tables only character codes at end of field names to tell them apart (LJ, RP, CB, AM and SD). A single Reviewers table with field for this 2-character code might be the answer. Only you can answer if you want to capture Reviewer’s name; if your file is only for statistical comparison, maybe not … is that what the two characters represent? The reviewer’s initials?

2. I don't think it would be best to instruct them to type in 1 or 0.

Create Custom Value List maybe called Select and only type 1. Attach to number field and display as checkbox toggle of 1 or blank. Set the checkbox (px size) to same width as height to hide the ‘1’ and then type whatever label you wish to describe the field it is attached to such as: Contract returned? Requested review?

When you want to display the field and have it display again as text, use number format (Inspector > Data Formatting > Format Boolean and type the value such as Yes (max 7 characters unfortunately). You can also use value list with 1 and 0; many options/combinations available. There are other benefits to use 1/0 or 1/blank as well.

3. I haven't tried an import script before. I'm not sure I understand where I'd put that or how to write it. Do you know of any reliable tutorial on import scripts, specifically?

Search FM Help for “Creating scripts to automate tasks” and then play with them in test file, go to right to import scripts. You will pick it up quickly. :^)