Can't create relationship

[Ninamashr]

FMpro Advanced ver 12.0v4,     I created a new database "Calibration Assets (CA)", which I imported some data into from an Excel spread sheet( filemaker assets list 7_8_14.xls).  I created another database " Calibration ( C )" and would like to pull in some data from CA.  When I try to create the relationship in C, I pick the CA db in the specify table window and when the file opens it's blank?  Where are the fields?  I have attached a jpg so you can see what I am looking at.  FYI, I was able to create a relationship to the Excel spreadsheet, but, not the CA db.    

 

 

I really appreciate any help you can give me. 

 

Thx

Lupe

[Lee Smith]

Please update your profile to reflect all of your current information, i.e. FileMaker version, OS, and platform. Here is a quick link for you MY PROFILE.

[LaRetta]

Hi Nina,

 

Notice how the Calibration table occurrence is regular text but the other two are italic?  The italic indicates that those table occurrences are from another file.  I suspect that the FileMaker_Assets table occurrence was the excel you created originally.  Then you created Calibration in THIS file.

 

The way to sort it out ... click the top left arrow in the table occurrence.  This will tell you the file the table occurrence belongs to.  You can check further by opening File > External Data source to see the paths to those files.  It can be confusing at first.  But there is no need to use two separate files in this instance.  :-)  

[Ninamashr]

LaRetta/Lee Smith

 

     Lee i've updated my profile as requested.  LaRetta thank you for the help you are giving me.  When I finished creating the Calibration Assets db I imported the raw data into it from the excel file you mentioned,  I then created the Calibration db and imported raw data into it from a different excel file. I created the relationship from the excel spreadsheet you mentioned just to show that it was working for the excel spreadsheet, so what should I do.  Should I delete the data in both db's and re-import the data from the 2 excel files?  I'm sorry i'm not real familiar with the Table occurrence side of FM.  

 

thx

Lupe 

[LaRetta]

Hi Lupe,

By looking at the tables tab (File > Manage > Database) in your Calibrations file, you can see how many records exist in each table which is in this file and we know you only have one because it is not italicised. To normally work in this table, you need a layout based upon this table. In layout mode, select Layouts > Layout Setup and from General tab, it shows the table occurrence it is based upon in a popup called 'show records from'. FM creates a layout when you add a new table to the graph so you should already have a layout which is default-named to that table occurrence (so in this case it would be Calibrations).

The two table occurrences you see in your graph to the right represent A) a different single FM file with two tables, 1) FileMaker Assets 7_8_14 with many fields and 2) Calibration Assets which has no fields, or B ) two separate files which each holds a single table; I suspect it is a single file. I would bet FileMaker Assets contains records and since Calibration Assets has no fields, I doubt you have any records in it.

So how should these two (or potentially 3) files be merged?

Well, you are currently in the Calibrations file so you will want the FileMaker Assets file imported into your Calibrations file as a new table. But first, some important housekeeping - you want to remove remnants of those other file(s) tables:

• In your graph, delete the two italicised table occurrences (the two right occurrences called FileMaker Assets 7_8_14 and Calibration Assets).
• Go to File > Manage > External Data Sources and delete anything there.
• If there happens to be any layouts based upon those two table occurrences, delete those layouts. All you should have is a single layout based upon Calibrations. Verify that the layout is correct by what is described above in bold on how to normally work on a table.

Now your file is again clean and is ready to bring in your other data. You can accomplish this easily by importing and specifying during the import, to 'create a new table' which is a selection in the popup box (at the bottom) on the Source TARGET side during an import. So stepping through, it would look like:

• Select File > Import > File and Open your FileMaker Assets 7_8_14.xls.
• On the left is the Source (open that popup if FileMaker Assets doesn't show there.
• On the right is the Target. Now ... if the target table does not exist (as in this case, it does not exist in this file because you want to create it), you can scroll to the bottom of the Target popup and notice 'New Table" and it will name it the same as Source (FileMaker Assets).
• Notice lower left shows "ADD records" and all the fields map across (that can be mapped).
• Lower right now ... specify IMPORT

What happens during this type of 'New Table' import:

• It adds that table into your File > Manage > Database 'Tables' tab.
• It imports all the records from Excel (and maybe some trailing blank ones which you might have to clean up).
• It adds an occurrence of that table into your graph. It will default name it same as the table name.
• It adds a layout based upon this table occurrence and names it same as your table name.

These default names will change according to the first created table occurrence of any table. I keep these 'special' tables aside and preserve their layouts as well. These become my BASE table occurrences which will make more sense down the road.  If you change the name of a table, these default 'first occurrences of the table' will change names as well.

One final thing to remember with FileMaker ... what appears in the graph is only a graphical representation of a table ... an occurrence of it. You can have more than one occurrence of a table in the graph. It does not mean the table exists twice but rather that you have replicated the graphical representation of it to model and configure different types of relationships in the graph.

Well, I suggest you walk through this information to understand it and then complete your import of the new table. These are all important basics to working in FileMaker. If you have questions after your import, please let us know. :-)

 

Edited:  corrected mistype - see red above

Edited July 19, 2014 by LaRetta

[Ninamashr]

LaRetta,  thank you so much for all your help and patience.  I have cleaned up my db's as you mentioned and re-imported the raw data into my Calibration Assets from the spreadsheet.  The fields on the new layout I created in the Calibration db do get pulled in but when I create a "new record" in the Assets db and attempt to import THAT new record into the Calibration db it will not import?  It's like any raw data records that were imported from the spreadsheet will import but none of the new ones created in the assets db will not?  I guess this version of Filemaker has changed, it seems much more complicated, I used to be able to just import raw data into my db's without having any tables referenced, layouts created, etc.   Is there anyway to do an import data besides the "New Table" import?   

[LaRetta]

'New table' import is only a once-off thing to bring in legacy data because it also creates the table and fields at same time it imports the data.  Now that you are in FileMaker, you don't use the 'new table' import any more.  If you are on a layout based upon Assets and you manually create a new record, it will create the record in Assets.  Why would you want to then 'attempt to import THAT new record into the Calibration db'?

 

The purpose of a relational structure is to eliminate redundancy.  Are you familiar with portals?  In your graph in the relationship between them, check 'allow creation' on the Calibrations side.  Then place a portal of Calibrations on your Assets form layout.  Place your Calibrations fields (all except the AssetID) in the portal and enter your new Calibration information for that new Asset.

 

 BTW, I know FM says 'database' but truly it is clearer if you refer to TABLES instead of DB.  There can be many tables in a FileMaker database FILE.  And what you discuss are tables within that file.  If you use the word 'db' it will cause confusion here in the FileMaker realm.

 

Also, you should use meaningless IDs for the key fields to relate your tables (instead of the AssetID).  It would be very helpful (for you and us both) if you would read through FM Help and walk through the tour to get up to speed on the basics.  The structure has changed since version 7 which came out (I believe) over 8 years ago.  

[eos]

[…] read through FM Help and walk through the tour to get up to speed on the basics. The structure has changed since version 7 which came out (I believe) over 8 years ago.  

 

How times flies when you're having fun …  t'was 2004, so make that 10 years.

 

btw, my tour into “The Realm of 7” was the “QUE Special Edition” doorstopper of a book, which for its greater parts lives on in the Jesse Feiler FM12 book; heartily recommended.

[LaRetta]

I originally started to say 9 years but I was unsure whether 9 or 10 and was too tired to look it up, and I dislike splatting incorrect information so I changed it to 'over 8', LOL.  Indeed, time flies!  And since a new version of FM is now coming out at a rate of one every 18 months, it's hard to keep them all straight.

 

I'd pay a fortune for a book by Comment ... no joke at all!!  A fortune! 

[Ron Cates]

@ LaRetta  Me Too!!!