Join & Export 4 Tables

[fm8443]

I have 4 Tables of Sales Records (1 per Store) for the same 1000 products (each sold in the 4 Stores).

 

Table1 (T1) has 30,000 records

T2 has 50,000 records

T3 has 10,000 records

T4 has 10,000 records

 

The fields in the 4 Tables have different names and contain similar “core” content.

 

Tables are added to each month with additional records (imported into FM 14 Pro Advanced from Excel).

 

Unfortunately the same Products are named slightly differently in each of the 4 tables.  Each store / table has a (text) Product-ID but (for the same product) it is different per store.

I do want to rectify this “naming problem” (perhaps in a Table #5?)  so I can produce a report showing sales for each of the 1000 products across all 4 stores (grouped by date etc.)

I’m not sure how to structure/carry-out this in Filemaker most effectively.  Any ideas?

 

Ultimately, when the data is clean, I want to upload ALL the records (e.g. 100,000 records) in one export into one Excel spreadsheet so I can run a Pivot Table (Cross-Tab) report.

 

Do I need to combine the 4 Tables into 1 Master Table before doing an Export on the Master Table?

If so, how would I do this?

 

Thanks for any ideas about this?

[comment]

I'm not sure what exactly your question is. Clearly, there should be only one Sales table. If a product can have multiple IDs, they should all be listed in the Products table, so that each record in Sales can be related to the appropriate record in Products, no matter which of the IDs it happens to be using (I am assuming here that the IDs are unique overall, otherwise it gets slightly more complicated).

You cannot export records from more than one table at a time.

[fm8443]

Thanks.  Yes, the Product IDs are unique overall.

If I understand you:

-- I should upload all 4 stores records into ONE Sales Table of 100,000 records (rather than the 4 tables described previously).  This should be easy to do by mapping the fields when I do the Excel uploads etc.

-- I should create a PRODUCTS Table (1000 rows and 4+ columns with the 4 Product ID's and a "Real/Definitive Product Name").

So I need to figure out what joins I need between these 2 Tables.  
And how do I combine the "Real Product Name" with the Sales Records into 1 Table (100,000 records) ready to Export to Excel?

Apologies, my RDBMS schema skills are very rusty.  

 

 

[comment]

Yes, you should import the records from the 4 tables into a single Sales table, where you would also have a StoreID field. You might want to do this through a temp table, so that your imported records end up in chronological order.

You will also want to make sure that any additional records imported into the combined table are populated with the appropriate StoreID by your importing script(s).

 

29 minutes ago, fm8443 said:

-- I should create a PRODUCTS Table (1000 rows and 4+ columns with the 4 Product ID's and a "Real/Definitive Product Name").

You only need one ProductIDs field (Text), and populate it with a return-separated list of all the product's IDs. So the relationship will be:

Products::ProductIDs = Sales::ProductID

Note that the Sales table does not need a ProductName field, or any field that describes the product other than ProductID.

 

29 minutes ago, fm8443 said:

And how do I combine the "Real Product Name" with the Sales Records into 1 Table (100,000 records) ready to Export to Excel?

When you export from Sales, you will include the Products::ProductName field in the field export order.

Note that Filemaker can produce a summary of your sales too, although it's not very good at cross-tab reports.

--
P.S. I don't know what your skill level is. Make sure you have a backup before you attempt a massive data migration like this. It's quite possible you'll have to do it several times before you get it right.

 

Edited August 31, 2016 by comment

[fm8443]

10 minutes ago, comment said:

You only need one ProductIDs field (Text), and populate it with a return-separated list of all the product's ID.

The 1000 products will get added to over time.  The ideal way for me to get this data (and send it to the PRODUCTS table) is to grab the unique values from the FM SALES Table.  How do I query the SALES table (on the Product-ID and the Store-ID) to add-to my PRODUCTS table just the unique products?

[comment]

If you define a field in the Products table to validate as «Unique, Validate always» then import into this table, only unique values will be imported.

 

[fm8443]

3 hours ago, comment said:

If you define a field in the Products table to validate as «Unique, Validate always» then import into this table, only unique values will be imported.

 

I've just done this and it works.  Due to data entry errors I need to refine the validation using 2 fields (P_NAME and P_ID) of the 4 fields in my PRODUCTS Table.  Not sure how to do this in the "Validated by Calculation" for the P_NAME field.  

I need something like this:

(Only) IF P_ID isempty THEN require P_NAME to be unique (before importing this "unique" record into the PRODUCTS table).

(Note: There may be more than 1 product name exactly the same and should be imported here IF it's P-ID is unique).

Edited August 31, 2016 by fm8443

[webko]

Create a temporary field that is a concatenation of the two fields above and apply the validation to that...

[fm8443]

51 minutes ago, webko said:

Create a temporary field that is a concatenation of the two fields above and apply the validation to that...

Do you mean create a new field called EXCLUDE_DUP that is = P_ID & " " & P_NAME ?

What do I validate on?  Field Options on this Calculated field doesn't have a validation option?

Also do I have to delete all rows and re-import to trigger the validation?  How do I re-trigger it?

[webko]

Make it a text field, apply the validation and the use Replace Field Contents with the Calc in the third option of the Replace Field Contents... That should trigger the validation 

[fm8443]

27 minutes ago, webko said:

Make it a text field, apply the validation and the use Replace Field Contents with the Calc in the third option of the Replace Field Contents... That should trigger the validation 

OK so I have deleted and recreated EXCLUDE_DUP to be a Text field.  Do you mean I should choose "Calculated Value" and set it = P_ID & " " & P_NAME on the Auto-Enter panel?  And I have set the Validation = Unique on the next panel.

Also, I don't see / understand what you mean by: "then use Replace Field Contents with the Calc in the third option of the Replace Field Contents... That should trigger the validation " 

Edited September 1, 2016 by fm8443

[webko]

Not an auto-enter calc (in this case) - just a standard textfield 

Then select the field and use the Replace Field Contents command ( Cmd + = ) and use the 3rd option shown to replace the contents of the field with a Calculation:

 

[fm8443]

Thanks, now I understand.  I fixed the issue with my method as well in the meantime.  Had to delete and reimport though.

[fm8443]

On 31 August 2016 at 0:57 AM, comment said:

You only need one ProductIDs field (Text), and populate it with a return-separated list of all the product's IDs. So the relationship will be:

Products::ProductIDs = Sales::ProductID

I have added the unique product rows from 2 of the stores into my PRODUCTS table and am now trying to further manipulate this table into what you describe here.  Not sure how best to do this now (and will need to be done in the future when new products are added).

EXAMPLE DATA -- PRODUCTS TABLE

P_NAME     P_ID           STORE_ID
Iron             96456         store1
Iron-Zenith  ACVDE45   store2
(etc.)

Note:  These 2 Iron products are exactly the same.  I want them referred to in my reports as "Iron-Zenith"
Where do I define (perhaps manually) which products are indeed the same?

How do I create the return-separated list in the P_ID from these rows?  e.g. 96456 <return> ACVDE45 <return>

So I guess my PRODUCTS Table (or a derivative table) would be processed to become:

P_NAME              P_ID             
Iron-Zenith           96456 <return> ACVDE45 <return> 
(etc.)

Do I need STORE_ID in there as well?

All to be done so I can do the JOIN as mentioned above....

Edited September 1, 2016 by fm8443