6 Replies Latest reply on Aug 25, 2010 12:36 AM by RafalKwiatkowski

    Sharing data between databases

    RafalKwiatkowski

      Title

      Sharing data between databases

      Post

      I have 3 DBs:

      - People Management.fp7

      - Invoices.fp7

      - Task Management.fp7

      1. In People Management.fp7 i have a list of my freelancers. In Invoices.fp7 i have a list of my clients. When we go to Invoices tab we can choose a client from a drop down list (list contains clients predefined in Clients tab). I wish to get possibility to choose from a drop down menu one of my freelancer from People Management.fp7 when creating a project and assigning this freelancer to task in Task Management.fp7

      The other possibility is to remove/delete whole contacts tab from Task Management.fp7 and when choosing personell and assigning the people to task in pop-up window i wish to see the people from People Management.fp7

      2. When creating a invoice i wish to be able to choose from a drop down list (or pop-up window) the project (from Task Management.fp7) as a entry/record on the invoice products list.

      3. I would like to be able to see in People Management.fp7 the list of the tasks/projects assigned (all not only open) to concreet vendor.

      Is it possible or did i made a mistake creating 3 separate DBs?

        • 1. Re: Sharing data between databases
          philmodjunk

          3 separate DB's is definitely complicating the process for you. Merging at least the data (Merging the entire file is a lot more work), looks like your best approach here. You'll need to compare tables bewteen People Management and the Customers table in Invoices and see if the fields defined are the same or can be changed to be the same. The names do not need to be identical--though this can help--but the type of data stored and any auto-entry and validation settings should match.

          You'll also need to manage serial numbers carefully as you likely have serial number fields in each table and records in each that have the same number as a record in the other table. If you haven't yet put real data in your table you can skip this part, but if you have, you'll need to change the serial numbers of all the records in one table so that they are different from the serial numbers in the other field. You might change the numbers from 1, 2, 3... to 1.1, 2.1, 3.1... for example. Not only that but you'll also need to change the values of any related records in other tables in the same way so that they stay linked to the correct records.

          Once you've done all that, you can use import records to import the records from the table you are no longer going to use into the table you plan to use for both files. Once you've imported the data, you'll need to find every table occurrence (the boxes in Manage | Database | Relationships ) that refers to the file you are no longer using and reconnect it to the table in the other file that you are keeping. Double click the box and use Add Filemaker Datasource to find and select the table in the other file.

          Finally, you'll need to click through all the layouts were fields exist that referred to the table you just removed and you'll need to double click any that no longer refer to the correct field and re-specify the correct field from the other table.

          I realize this is not a simple process for someone new to FileMaker, but it can be done if you are careful.

          • 2. Re: Sharing data between databases
            FentonJones

            I don't see that there is necessarily any real "problem" with Rafal's structure. I don't see anywhere that he is entering data into the same kind of table in separate files. So I don't see conflicts with serial ids (which admittedly, would be a PITA to fix).

            Multiple files are in some ways neither here nor there. Many of us support multiple file solutions (don't build them much anymore, but support them). It is not "files" which matter, it is "tables". Any file could contain multiple tables. Any file could contain any or all table occurrences of all tables of its file or all files. 

            In this case I'd really recommend only 1 file, with all tables. The things Rafal is talking about all partake in one area of processing.

            I'd also recommend you use FileMaker Pro Advanced. It is the right tool for real development. It would make a merge of all files into one easier. But FileMaker Pro would do. You can Import an entire table. You can Import Scripts.

            It seems there are Clients (in Invoices file), and freelancers (in People). Apparently they do not overlap. 

            So mostly it seems Rafal is asking if it is possible to do what he wants. The answer is "yes", it is common as dirt :-] 

            He also seems to be asking how to do it. That is kind of several questions. It would be better to ask one at a time. Because the structure and method to do one is similar to another. It is more about understanding basic relationships, and how to use them when entering data. 

            There seems to be a fair amount of confusion here; both in the original question (which has an odd mixture of structural and layout objects) and in Phil's answer. Rafal. You mention "projects", but you do not say where or at what level of the structure they are. Is an "invoice" a "project", can there be multiple "invoices" for a "project"?

            • 3. Re: Sharing data between databases
              philmodjunk

              I suggested merging tables for this reason:

              In Invoices.fp7 i have a list of my clients. When we go to Invoices tab we can choose a client from a drop down list (list contains clients predefined in Clients tab). I wish to get possibility to choose from a drop down menu one of my freelancer from People Management.fp7.

              Thus Rafal appears to have similar data in two different tables of two different files. Merging that data would be the best way to handle this going foward and I totally agree that it will be a pain to do--though it need only be done once.

              • 4. Re: Sharing data between databases
                FentonJones

                Rafal said:
                "I wish to get possibility to choose from a drop down menu one of my freelancer from People Management.fp7 when creating a project and assigning this freelancer to task in Task Management.fp7"

                So there's more to that sentence. It does not necessarily mean an "overlap" with Clients. I think he meant he wanted to assign a "freelancer" to a "task" (though just how a "project", an "invoice", and a "task" interface is not clear; a project is not defined at all).

                I'm assuming that multiple Tasks could be added to one Invoice, via a portal on the Invoice layout, and that a "freelancer" would function much like a "staff"person in this regard (is only 1 freelancer assigned to a task, or can multiple freelancers be attached to the same task?).

                • 5. Re: Sharing data between databases
                  philmodjunk

                  Good point.

                  Rafal, I think it's up to you at this point to let us know what you want.

                  • 6. Re: Sharing data between databases
                    RafalKwiatkowski

                    Thank You for the suggestions. Here is some clarification. See the picture. The red onces are ready to use because these are implemented into starter solution but i don't have idea how to make such new drop-down lists with auto-fill. The blue one are these i wish to get. Green numbers are for reference:

                    1. (see the blue line) i have to get all data/tables from People Management.fp7 to Task Management.fp7, customize layout and then i should be able to import all records exported from People Management.fp7 into Task Management.fp7 - i think i know now how to do this. After this the file People Management.fp7 won't be necessary anymore - the first and third idea/need (from my first post) will be done

                    2. this is the best thing ever - You can choose client from drop down list and all other details are filled automatically. How to create such things?

                    3. when creating a invoice i can select a product from product catalogue, i wish to be able to select identically the project created earlier but this is not critical.

                    I think this is all for now. :)