Jump to content

Join & Export 4 Tables


fm8443

This topic is 2791 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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?
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.  

 

 

Link to comment
Share on other sites

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 by comment
Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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 by fm8443
Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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 by fm8443
Link to comment
Share on other sites

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:

 

Screen Shot 2016-09-01 at 10.52.37 am.png

  • Like 1
Link to comment
Share on other sites

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 by fm8443
Link to comment
Share on other sites

This topic is 2791 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.