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.
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
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
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:
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.....
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.
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)
2011 sales figures
Lefthanded widgets: 300 $20,000
Beano Buckets: 200 $2,000
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:
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.
Thank you for your responses
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."