9 Replies Latest reply on Mar 30, 2012 3:35 PM by SeamusDoone

    Creating Multiple interacting databases.

    SeamusDoone

      Title

      Creating Multiple interacting databases.

      Post

      I am attempting to add a layout/seperate database into another database.  I know how to do this and how to make it seemless to go from one to the other.  My problem is making them interact properly.

      The problem is that I have to take info from database A and rearrange/extract some of that information to excel in order to place into my new database, we'll call this database B.  So database A records every transaction and creates a new record for each one.  Database B compiles this information to list the total number of transactions associated with each customer.  So in Database A there could be 10 seperate records containing details about each transaction and in Database B there is one record containing total customer transactions, so the one record has 10 transactions with product details etc.  

      In order to acheive this I have exported the data from Database A into an Excel file to reorganize and condense the data.  Is there a way to make this even more seemless?  Basically my goal is to have Database B account for additional records being created in Database A by recognizing the name on the new record and adding 1 to the transaction total in Database B.  So if there were 10 transactions/records in Database A for Customer A I want Database B to recognize Customer A and simply add one to all coorelating transaction totals.  So if Customer A purchases Product A a new record in Database A would translate to an addition to the total in Database B.

       

      Can anyone help me with this?

        • 1. Re: Creating Multiple interacting databases.
          philmodjunk

          It's easier to analyze your problem if you share the actual names/functions of your tables than use letters A and B.

          "Database" is a very fuzzy term in FileMaker land. Depending on who is using the term, it could refer to a table, a file or a group of files. From context, I'm guessing you have two tables each in a separate file.

          I'm going to refer to the table in File B as "Customers" and the table in File A as "Transactions".

          Why do you need separate files? Nothing intrinsically wrong with that approach, but it does complicate the design of your solution so it's best to avoid using separate files unless you have a compelling reason that justifies the added complications. Putting both tables inside the same file makes working with data a bit simpler and can greatly simplify managing password controlled access to your database system.

          I don't really see any need to export any records from one file to the other. Even with the tables in separate files, you can define this relationship:

          Customers::__pk_CustomerID = Transactions::_fk_CustomerID

          To set up this relationship in one of your two files, you can open manage | database | relationships and use the very far left button on the bottom to add a new table occurrence and then you can add an external FileMaker data source that refers to a table in the other file. From that point, you can work with the two tables as though they are defined in the same file.

          Using this relationship, a calculation field in Customers can use Sum ( Transactions::Amount ) to compute the total transaction amount for that one customer, or you can refer to a summary field defined in Transactions as the "total of" this same amount field to get the total for each customer. The totals will update automatically each time records in Transactions are added, deleted, or changed.

          And you can also use such a summary field on a layout based on the Transactions table to display subtotals for every customer--with or without listing the individual transactions. The way to do that is with a list view layout where you add a sub summary part "when sorted by" either _fk_CustomerID or a name field from the related Customers table.

          • 2. Re: Creating Multiple interacting databases.
            SeamusDoone

            Thank you for your response

            Ok so I know how to put the tables into one file, what I am trying to figure out is everytime a new record is entered in Transactions this is reflected in a single record in Customers.  The Customers table should not add a record only update the one in it.  In Transactions it appears as "Product Use: Prodcut A" and in Customers I want it to add one to the total already existing in Customers table.  

             

            The problem is that there is one field with a text value in Transactions.  There are multiple fields in Customer with calculated values, numbers.  I need Customer to recognize the text in Transactions "product use" field and add 1 to the total in Product __ invoice field without adding a record.  I am trying to do this with an IF statement but am getting held up with the add 1 aspect of the equation.

            So far:  Product A (Field)=IF(Transactions:Product Use="Product A", "(I want this to add one to the toal in Product A field)", "(I want this to do nothing if there is a different prodcut in Product Use)")

             

            Can you help me with this

            • 3. Re: Creating Multiple interacting databases.
              SeamusDoone

              It would also have to incorporate dates.  In other words these invoice totals in the single record of Customer are broken into years with product totals for each year.  So all new Product A's would only go to 2012 Product A

              • 4. Re: Creating Multiple interacting databases.
                philmodjunk

                While this can be done on the customers table, it requires creating either multiple relationships or multiple filtered portals to transaction totals. And no data would be modified in the customer record--that's rarely a good option here, instead the relationship accesses the correct subset of data in the related table in order to compute the needed totals, averages, counts etc.

                What's far easier to do is to set up a summary report based on the transactions table with fields from the related customer table included on the layout to provide details about that customer. Summary fields compute your totals and will display subtotals for the customer and different categories for that customer by putting those summary fields inside sub summary layout parts and then sorting your records to group them by Customer, year and category. This can give you a report that looks like:

                Customer: XYZ

                   2011 sales figures             Count         Sales
                       Lefthanded widgets:         300           $20,000.00
                       Beano Buckets:                200           $2,000.00

                  2012 sales figures              Count         Sales
                       And so forth.....

                Customer ABC

                   2011 sales figures             Count         Sales
                        and So forth...

                You can further control what records in transactions supply data to this report by performing finds before sorting the data.

                • 5. Re: Creating Multiple interacting databases.
                  SeamusDoone

                  Ok thank you for your answers I am not following you though.  I have that information in my Customer table, that is basically what my customer table is.  What I need to know is how to have the data being entered into my Transactions table translate to my Customer table. The Customers table is my summary report based off the information in my Tansactions table.

                  1)  Ok, so how would you do this: (In your example, my Transactions table would have a total of 500 different records associated with XYZ.  I would like to be able to seemlessly do this and condense that information as you have it here, as I have it in the Customer table but only because I exported the information into excel, rearranged it, and imported it into filemaker in this format.  How would I create a field i.e. "Left Handed widgets" in my Customers table/layout that would condense three hundred different records from the Transactions table into a single field as a number.  The values in the Transactions table are in text; so the 300 records would all have fields labeled "Product Use" that would contain the text "Lefthanded Widgets".  How can I seemlessly create a field in a different layout that automatically sums this information as you have it here)

                   

                  Customer: XYZ

                  2011 sales figures

                  Lefthanded widgets: 300 $20,000

                  Beano Buckets: 200 $2,000

                  • 6. Re: Creating Multiple interacting databases.
                    philmodjunk

                    This is not done on a customers layout. What I am suggesting is done on a layout based on the other table, but uses sub summary parts, summary fields and sorting to condense the data down so that "500 reocrds associated with XYZ" make up the much smaller number of rows shown in my example.

                    Please keep in mind that you've told me very little about how the data is organized in your tables. I could easily make wrong assumptions about it's structure at this point. If I were setting up a database, I'd have tables related like this:

                    Customers----<Invoices-----<LineItems>----ProductsAndOrServices

                    You appear to have a customers table and a LineItems table with out any invoices table and that makes me wonder if my assumptions about your data are correct.

                    If each record in your transactions table consists of one type of items purchased by one customer on a given date, then you might have these fields in the table:

                    CustomerID, Year, QtyPurchased, ItemID, ItemDescription, UnitPrice, cExtendedCost (QtyPurchased * UnitPrice )

                    You might also have a "category" field. "LeftHanded widgets" could be a description or it could be a category in my original example. The report methods work the same, but the implementation details vary. I'll leave that field out of consideration to keep the example as simple as possible.

                    To this table, add sTotalQty as a summary field that computes the total of QtyPurchased. Add sTotalCost as the total of cExtendedCost.

                    Create a new layout based on this same table.

                    Use Part Set up in the layout menu to add a Sub Summary layout part. Specify that it is "when sorted by" Customers::cFullNameField. Click OK to leave dialogs until you get back to the layout. (Select Print above.) Add the Customers::cFullNameField to this sub summary part.

                    Return to part setup and add another sub summary part, When Sorted By Year. (Year, can be a calculation field, year ( datePurchased) ). Put the layout text "2011 sales figures" in this sub summary part.

                    Now add a third sub summary part, when sorted by ItemDescription (you can also choose ItemID). Put the ItemDescription field and the two summary fields inside this sub summary part.

                    Finally, click on the label for the body layout part and press the delete key to remove it. This is the step that drops out the individual records so that you only get rows of data for each summarized group of records.

                    Now enter browse mode and sort your records by customers::cFullnameField, Year, and ItemDescription in that order. If you do not include a "when sorted by" field in the sort order that specific sub summary part will not appear. This can be confusing when this layout look blank when no records are sorted, but it can provide some interesting options when you control which sub totals appear in your report just by changing the sort order specified.

                    Here's a tutorial on Summary reports you may want to examine: Creating Filemaker Pro summary reports--Tutorial if you have questions about this tutorial, feel free to either post them here or start a new thread for asking those questions. If you post a question to the tutorial thread, its age keeps it from reappearing in the recent items list when new posts are made to it and I'm likely to miss your questions.

                    • 7. Re: Creating Multiple interacting databases.
                      SeamusDoone

                      Thank you for your responses

                      • 8. Re: Creating Multiple interacting databases.
                        philmodjunk

                        Hmmm. spotted a mistake in my last post: "Put the layout text "2011 sales figures" in this sub summary part."

                        That should read, put the Year field and the the text: "sales figures" in this sub summary part."

                        If you did like I first described, all sub heading would read 2011 even if they represent data from a different year.Embarassed