Discrepent Record Content Between Two Related Tables

[nopposan]

Hello, all. Total novice here.

Please tell me how I can be sure that when I import records to my database table they will automatically be created in a related table.

I have two tables in separate FileMaker database files. I've related them via the relationships gui map that's provided; I've created a relationship matching/equating ID numbers that allows records to be created in one table whenever records are created in the first table. However, I suspect that when I import new records into the first table from an Excel file those records aren't automatically added to the related table. I have hundreds of records that show in the first table and not in the other.

Thanks in advance for whatever help you can offer.

Edited April 3, 2008 by Guest
'Forgot to mention something.

[David Jondreau]

1) The "Allow creation of related records" option does not create records for you, it simply allows you to create records.

2) Why are you creating the same records in two different tables? At first blush, it sounds unnecessary.

3) You will probably have to import twice.

[nopposan]

Why? Why??!

O.K., I don't know why I'm creating records in two different tables. Well, then maybe I do; the first reason is that I don't know any better, and the second reason is that the tables had separate origins as separate Excel files that I imported.

'Created totally separate database tables in separate Fmp files as that's what my masters requested. Also, it did seem from what little reading I did that it would ultimately provide the most flexibility on a project that's going to expand indefinitely.

Cheers.

[Fenton]

It makes little difference whether it's separate FileMaker "files" or not; it's the tables that matter. So your "masters" may or may not really understand relational database design. If you need two separate tables, then, as DJ said, you'll have to import twice. If the 2 tables are supposed to be related, then you'll need a field (column) in each Excel file that could be used to create a relationship to tie them together.

But we can't really tell you anything definite about any of this from only the fact that you have 2 Excel files, except that you can't import into 2 tables at the same time. Though a script could do 2 imports, without you having to do it manually, if it knew the paths to the files, and the order of the fields (columns).

[nopposan]

Though a script could do 2 imports, without you having to do it manually, if it knew the paths to the files, and the order of the fields (columns).

Hmm. . .

Thanks, Fenton, I clearly need to read some things on your website. Feel free to offer suggestions on what to start with.

So many things rolling around in my mind now. O.K., before I digress, and I will . . . Once I get my database tables cleaned up so that they include the same ID numbers, then how do I make sure they stay that way? Should I ultimately replace the secondary table's ID field with a calculation field saying ID=Table1::ID ?

Now, don't worry about the 2 Excel files right now. One of them has been retired, so we're only adding new info via the first one. But -- now back to why I quoted Fenton -- it would be nice to know how to write a script that automatically imports select fields from an Excel file.

Now, the digression. Oh, see above regarding imports from Excel files; has little at all to do with my original post.

Cheers.

[Fenton]

I'm afraid we're kind of suffering from confusion of terms. We cannot really talk about IDs until you understand a bit about how relationships work, and we have some more concrete examples to talk about. Right now I don't have any idea what data you would want to relate, especially as you now say there's only 1 Excel file; which we have no idea what it looks like. Excel files can have any kind of logic (or even no logic -).

When you say, "it would be nice to know how to write a script that automatically imports select fields from an Excel file", I don't know whether you mean "how to tell FileMaker which file to import," or "how to select columns to import". They are very closely related questions.

You tell FileMaker which file to import (in a script), by setting its path, in FileMaker syntax, into a Script Variable (it's a script step, look it up). For example, this is the FileMaker path to a file in a folder on my Desktop; it is an "absolute" path, which is what you'd need with hosted files.

filemac:/Macintosh HD/Users/fej/Desktop/TMC_Books_Export/Taxes_FIN_2008.xls

Yeah, it's a Mac path. But the trickiest part is available via a FileMaker calculation function, Get (DesktopPath), which would produce: /Macintosh HD/Users/fej/Desktop/

Notice that it includes the beginning and ending slashes. Also that it includes your OS user name (fej in my case), and allows unescaped spaces.

On Windows you'd get something like:

/C:/Documents and Settings/fej/Desktop/

The whole path would look like:

filewin:/C:/Documents and Settings/fej/Desktop/TMC_Books_Export/Taxes_FIN_2008.xls

There's also Get (DocumentsPath), which you can imagine. So, you'd set that into a script Variable, with a name like: $filepath

(whatever you want; keep it short).

Then you'd use that ($filepath) as the file path in the Import dialog. However, you would want to also keep a real path to a real file, as one of the lines in that file path box (which supports multiple lines, of paths to try). You need a path to a real file so that you can set up the Import Order; which is where you match the Excel column names to the FileMaker field names.

You must either have the Excel column names as the 1st row of the file; or you can have them as the 1st row of a defined Range in Excel. That is one of the Import options, worksheet or named range.

If however you've jammed different "entities" (things) into different Ranges in Excel, then, once again, you're dealing with things that belong in different tables. Excel lets you do whatever the heck you want, so we can't say much more.

[nopposan]

I'm afraid we're kind of suffering from confusion of terms. We cannot really talk about IDs until you understand a bit about how relationships work, and we have some more concrete examples to talk about. Right now I don't have any idea what data you would want to relate, especially as you now say there's only 1 Excel file; which we have no idea what it looks like. Excel files can have any kind of logic (or even no logic :-).

We agree about the potential for illogic.

'Guess I'd better straighten some things out. My initial question was really regarding just one Excel worksheet. However, the relational database I'm initiating depends on information that originated in three different Excel worksheets; worksheet #1 for patient and client contact information, billing information, sample information (including the assigned sample ID), and test ordering information, worksheet #2 for clinical information provided and our test results, and worksheet #3 for testing progress information. However, my assignment was to break down worksheets #1 and #2 and import the data into separate database tables in separate files. I've also been told that worksheet #1 must be the prime worksheet; all new records must begin there. Also, we're not interested in the billing information in my department, so those columns for worksheet #1 went. Now I've got the following: 1) "SampleLog" A database table based on an import of a derivative cleaned-up version of worksheet #1 that contains only the patient name, sample and test ordering information from worksheet #1 that we want. 2) "PatientInfo," a database table that will contain both the information from "SampleLog" and additional information about the patient and referring physician that we don't yet input into electronic format. (I know it's redundant, but I created "SampleLog" to demonstrate to my superiors that I'm doing what they want me to.) 3) "ClinicalProvided" is where all the good stuff about medical and clinical information goes. 4) "TestProgress" has mostly date fields for milestones in the testing workflow. 5) "TestResults" . . . contains test results and final conclusions.

I've related these five tables by making the ID number (a text field) a match field, and the match is contingent on equality.

Thanks for the further information about how to automate the importing of Excel files; I'm not going to tackle that right away though.

'Gotta go now. More later.

[nopposan]

When you say, "it would be nice to know how to write a script that automatically imports select fields from an Excel file", I don't know whether you mean "how to tell FileMaker which file to import," or "how to select columns to import". They are very closely related questions.

I meant both. So, thanks for answering both, in general. When I need more particulars I may come back with more questions.

There's also Get (DocumentsPath), which you can imagine. So, you'd set that into a script Variable, with a name like: $filepath

(whatever you want; keep it short).

I'm not sure I understand this. We're not talking about importing from a MS Word document table are we? That could be useful. No, that's not what you're doing here, I guess. 'Seems like you are just telling me a way to set a variable that calls the file path. Yes? That's also very useful, so thank you.