7 Replies Latest reply on Aug 8, 2014 8:24 AM by philmodjunk

    Need to make separate files into one file to build relationships between table and interface

    DanielKnott

      Title

      Need to make separate files into one file to build relationships between table and interface

      Post

           Hi everyone, I am a bit of a FileMaker newbie (I used the program for basic databases in the beginning of college almost a decade ago). I am the IT Systems Admin at my company and we've been using Trackvia for a few years to accomplish our goals (for which I'll get into shortly) but find the cost becoming increasingly prohibitive as our company continues to grow exponentially. The President of the company wanted me to take a look at the possibility of using Filemaker to be able to track tasks/notes from each of our employees to our clients (the data of which can easily be imported from an existing Excel database). Anyway, I've set up a rudimentary task system that I've begun customizing that would fit our needs almost perfectly, however when I import an example Excel database, it creates an entirely new file. The problem is that none of the relationships that I build maintain upon closing; also, for the majority of users in the company, having a single file is infinitely easier for them. I've taken a snapshot of what I currently have so that someone may be able to point me in the right direction. Thank you in advance and if there's anything I can do to further assist (ie more information), please let me know. 

      FM_Ex.PNG

        • 1. Re: Need to make separate files into one file to build relationships between table and interface
          philmodjunk

               however when I import an example Excel database, it creates an entirely new file.

               This does not sound like you are importing, but rather that you are dragging the excel file over and dropping it on the FileMaker Application Icon. This file conversion process produces a copy of your excel file converted into a FileMaker File.

               But that's not your only option here. Once you have done this with one file, you can pull down the File menu and use Import Records | File, to import data from a second Excel file into your current FIleMaker file and this will append the data in that spreadsheet to the data from your initial conversion.

          • 2. Re: Need to make separate files into one file to build relationships between table and interface
            DanielKnott

                 I'm actually opening the Excel file via the File dropdown menu, from which it's then converting the file into a Filemaker file, but the function is the same I suppose. As for the import records function, it's not going to work the way you describe simply because I have a Task manager pulled from the Starter Solution that I need to have relationships formed between it and the imported Excel database. If I import the records of the database, for example, into my task manager that I've customized, all of my data becomes unusable because all the fields aren't (and shouldn't be) identical. I'm not sure if the image I uploaded provides a good example but currently I have the two "files" open in Filemaker where I've got the Tasks file and the Excel sheet converted file simultaneously. I can build relationships all I want, but the moment the program is closed, all of those changes are eradicated because it's two separate files. 

            • 3. Re: Need to make separate files into one file to build relationships between table and interface
              philmodjunk

                   I gather that you have more than one table defined in your database?

                   It would seem that you simply need to import this data into the correct table. You can select the layout for that other table and then select Import Records to import your data. If there is, as yet no table into which to import your data, there is a a "new table" option in the target table drop down that you can select such that your imported records create a new table. And after importing the records, you can go to Manage | Database | Relationships and link this new table to the others as you see fit.

                   But there could be issues assigning the correct match field values to your imported records in order to set them up with the correct links via your defined relationships. There are ways to handle that, but the details depend on the design of your database and the spreadsheets from which you need to import data.

              • 4. Re: Need to make separate files into one file to build relationships between table and interface
                DanielKnott

                      

                          I believe you were correct about the import, as I was able to import the company field into my Task manager system without causing conflicts within other areas, however the file for which I imported still contains more applicable data that I need for my Contacts setup, that doesn't need to pertain to the Task file. Due to the fact that they're separate schema (and separate files altogether) they still don't save the relationships I've built, which is still something that I need to have happen if possible. The original picture I've included shows that the converted Excel document, the Task interface, and Contacts interface (the latter two of which were pulled from the Starter Solution) are all separate files.

                          Perhaps I'm going about this all wrong, or perhaps lazily, but I want to be able to have the Contacts and Tasks visible and usable for my office users rather than a bland database setup (such as what TrackVia contains) to make it easier for them. If I need to build by hand what I've customized with the Task and Contact Starter Solutions, I can do that, I just need to know if it's possible to either merge the files or create a whole new solution that still has separate schema. Thanks again!

                      

                • 5. Re: Need to make separate files into one file to build relationships between table and interface
                  philmodjunk
                       

                            they still don't save the relationships I've built, which is still something that I need to have happen if possible. The original picture I've included shows that the converted Excel document, the Task interface, and Contacts interface (the latter two of which were pulled from the Starter Solution) are all separate files.

                       This is the part that makes no sense to me. When you close a FileMaker file all changes made whether to the tables or the relationships are saved and will be there when you re-open. As long as you import all data into a single file, you shouldn't have any issues with that FileMaker file retaining it's design. I don't see why you don't import your data directly into the starter solution that you have already modified to be the way that you want it--no file conversion needed.

                       If you are trying to recreate design elements of the Excel document, calculations, macros, different worksheets, then no these changes must be recreated by hand.

                       and you can import more than once from the same excel file, selecting different sections of that file as the source and specifying a different target table in the FileMaker file as the target.

                       But if you want to combine the features of two different starter solutions in a single file, that's additional design work that you will need to do in order to bring that about. That would be the case even if you had no data to import from excel files.

                  • 6. Re: Need to make separate files into one file to build relationships between table and interface
                    DanielKnott

                         Regarding your first paragraph; the reason that the relationships dissolve upon shutdown from what I'm gather is because they are indeed completely different files. I may have resolved this issue entirely, and again I apologize for being a Filemaker luddite. I have taken the Task Starter Solution and have begun importing information into new tables and layouts within that file. I know this is basic, but it's totally different compared to how I've been building databases with Trackvia for years (which in itself isn't an intuitive solution). 

                         Once I get all my layouts and tables into the same file however I have another two questions. The first being that, in my task starter solution, I'd like to be able to create a relationship to my table with my company and contact names where if I select Company from the drop down, then select the company, I only find contacts from that specific company. I've started looking at multi-criteria relationships but wanted to make sure that's the right avenue before going down the wrong rabbit hole. 

                         Secondly, is it possible to build relationships between layouts within the same file? I have imported my Excel data into a new layout in the same file as Task but I can't build relationships between these. I'm not sure if I need to then import that data into another table within the Task layout? 

                         Sorry again for the confusion and I greatly appreciate your help; at least now I have the data in one file thanks to your help :)

                    • 7. Re: Need to make separate files into one file to build relationships between table and interface
                      philmodjunk
                           

                                the reason that the relationships dissolve upon shutdown from what I'm gather is because they are indeed completely different files.

                           I really have no idea what you mean by "relationships" in that sentence. Any FileMaker relationships that you define, whether between tables inside a single file or tables in different files will be automatically retained when you close the file(s). I must conclude that you mean something different when you say "relationships" in that sentence.

                           Let's take your two questions in reverse order as you need to know how to deal with the issues of your second question before you can deal with the first:

                           

                                Is it possible to build relationships between layouts within the same file?

                           One never creates relationships between layouts in FileMaker. That's because you build relationships between tables rather than layouts. Here's the basic structure under the hood in a FileMaker Database file:

                           You have tables, table occurrences and layouts. All frequently have the same name because that's the default behavior of FileMaker, but they are not the same thing. Each of these objects has a different role to play in your database.

                           Open manage | Database | Tables, enter a new table name, "Contacts" and click Create. You have just created a new table named "Contacts" obviously, but FileMaker goes ahead and creates a layout and a table occurrence named Contacts for you. If you click the relationships tab, you'll see that a new "box" has been added here that is also named "Contacts". This is a table occurrence. It looks like your table, FileMaker will refer to it as a "table", but it's really a table occurrence--a way to refer to your table in order to link it to other tables in relationships. But it's not your table. It's not, because you can duplicate this table occurrence to get more table occurrences that all refer to the Contacts table. This is useful in setting up multiple relationships to the same table that specify different sets of match fields.

                           Now close Manage | Database by clicking OK. Pull down the layouts menu and you'll find that you have a new layout named "Contacts". This has the same name, but it's a layout, not a table. Select this layout, enter layout mode and open Layout Setup... from the Layout menu. Note the drop down named "show records from" in this dialog. This is where you select a Table Occurrence from Manage | Database | Relationships for your layout. FileMaker has already selected "Contacts" as the table occurrence for your layout. This sets up the "relationship context" for any data you choose to display on this layout FileMaker will evaluate the relationships defined in Manage | Database | Relationships from a "starting point" of the table occurrence box specified for your layout--Contacts in this case.

                           Just as you can have many table occurrences that all refer to the same table. You can create many different layouts that all refer to the same table occurrence--by specifying the same table occurrence name in this "show records from" drop down.

                           So you would "link your layouts in a relationship" by going to Manage | Database | Relationships and linking the table occurrences on which the layouts are based via their "show records from" settings.

                           Table Occurrences are a key structure to how your database functions and the more you know about them, the more you can do with your tables of data. To learn more about Table Occurrences, see: Tutorial: What are Table Occurrences?