Creating a return-separated list from rows in another table

[fm8443]

I have an FM Table called PRODUCTS (shown on lines 2-5 in the image file).
I want to transform this into an FM Table called TRANSFORMED (shown on lines 7-8 in the image file).

How do I do this in FM?

(I am indicating return-separated lists with tilde's (~) in the image.)

Also, does this TRANSFORMED table structure make sense?:  I intend to have a JOIN on the TRANSFORMED table's PROD_ID field with the PROD_ID in my SALES table for an export (to Excel) of the SALES table based on a match on one of the return-separated list values in the PROD_ID field.

 

 

Edited September 7, 2016 by fm8443

[comment]

Didn't we do this already?

1. Define the Real_Product_Name field in the TRANSFORMED table to validate as Unique, Validate always;

2. Import the records from PRODUCTS into TRANSFORMED, mapping Real_Product_Name to Real_Product_Name;

3. Define a relationship between the two tables, matching on Real_Product_Name;

4. Populate the PROD_ID field in TRANSFORMED by a calculated value = List ( PRODUCTS ; PROD_ID );

5. Change the relationship to match on PROD_ID.

I don't see why you need the other fields (STORE, PRODUCT, PM) in the TRANSFORMED table.

[fm8443]

11 hours ago, comment said:

2. Import the records from PRODUCTS into TRANSFORMED, mapping Real_Product_Name to Real_Product_Name;

What's the best way to do this Import?

Is it to Export out to Excel and then Import back into the other table after mapping the fields?  
Or is there a more direct way that's quick to setup inside of FM?  Thanks.

[comment]

9 minutes ago, fm8443 said:

What's the best way to do this Import?

You can import directly from one table to another in the same file. Start by going to the layout of the target table (TRANSFORMED) and from there select File > Import Records > File… and choose the current file. In the Import Field Mapping window, select PRODUCTS as the source table.

[fm8443]

On 7 September 2016 at 7:17 AM, comment said:

          1. Define the Real_Product_Name field in the TRANSFORMED table to validate as Unique, Validate always;

2. Import the records from PRODUCTS into TRANSFORMED, mapping Real_Product_Name to Real_Product_Name;

3. Define a relationship between the two tables, matching on Real_Product_Name;

4. Populate the PROD_ID field in TRANSFORMED by a calculated value = List ( PRODUCTS ; PROD_ID );

5. Change the relationship to match on PROD_ID.

I don't see why you need the other fields (STORE, PRODUCT, PM) in the TRANSFORMED table.

I've done all this and now trying to export the SALES table to Excel (including the corresponding Real_Product_Name).  
[Note:  SALES and TRANSFORMED Tables are linked (relationship) on PROD_ID in the graph & TRANSFORMED::Real_Product_Name contains return-separated text values]

Getting the Real_Product_Name is not working.  I've tried 3 ways.

1) Exported all fields from SALES table and also the field TRANSFORMED::Real_Product_Name.  (but Real_Product_Name are all blank in Excel)
2) Added a Real_Product_Name (Text field) to SALES table.  Made it a LOOKUP of TRANSFORMED::Real_Product_Name.  Validate Always.  I've unchecked the "Don't replace existing value" setting.  (No effect -- Real_Product_Name are all blank)
3) Same as #2 but made it a CALCULATED VALUE = TRANSFORMED::Real_Product_Name. Validate Always.  I've unchecked the "Don't replace existing value" setting. (No effect -- Real_Product_Name are all blank)

Any ideas about what I am doing wrong?

 

Edited September 17, 2016 by fm8443

[comment]

The relationship between SALES and TRANSFORMED  should be:

SALES::PROD_ID = TRANSFORMED::PROD_ID

Then you can export from the SALES table and include the TRANSFORMED::Real_Product_Name field in the field export order.

 

[fm8443]

1 hour ago, comment said:

The relationship between SALES and TRANSFORMED  should be:

SALES::PROD_ID = TRANSFORMED::PROD_ID

Then you can export from the SALES table and include the TRANSFORMED::Real_Product_Name field in the field export order.

 

I've done exactly this and it doesn't work.  The TRANSFORMED::Real_Product_Name field is all blank in Excel.

Tried it twice.  Exporting from the SALES Table (not the Layout), I add the TRANSFORMED::Real_Product_Name field to the Export fields...

I created an equal (=) relationship on the two PROD_ID fields (in the TRANSFORMED and SALES tables.)

(Could the issue be anything to do with the fact that PROD_ID in the TRANSFORMED table is a return-separated values while the PROD_ID in the SALES table is a TEXT field? BTW -- this does seem to be at least part of the issue as to why my LOOKUP didn't work.  I added a TEST_PROD_ID field to TRANSFORMED and it did do a simple test lookup.)

[comment]

2 hours ago, fm8443 said:

The TRANSFORMED::Real_Product_Name field is all blank in Excel.

If the related field is blank, than either it has no data or - much more likely - there is no related record. Check your data. Taking the example from your original post, if your SALES record is from the Tesco store, then the SALES::PROD_ID field should contain:

1532966504

and you should have a record in the TRANSFORMED table where the PROD_ID field contains:

B01ETBNKXY
1532966504
3644121086

and the Real_Product_Name field contains:

COTTON BUDS

Make sure you have no trailing spaces in either of the PROD_ID fields, as that too would prevent a match.

 

2 hours ago, fm8443 said:

Could the issue be anything to do with the fact that PROD_ID in the TRANSFORMED table is a return-separated values while the PROD_ID in the SALES table is a TEXT field?

No, that's exactly how you want it to be. Look up "multi-key field" in the help.

 

Edited September 17, 2016 by comment

[fm8443]

I am not seeing the data issues you mention.  Will keep looking.

Could this be the problem?

STORE_ID field in the TRANSFORMED table is:
Type: Calculation
Options: Unstored, = List ( PRODUCTS::STORE_ID )

[comment]

5 minutes ago, fm8443 said:

Could this be the problem?

STORE_ID field in the TRANSFORMED table is:
Type: Calculation
Options: Unstored, = List ( PRODUCTS::STORE_ID )

No, i don't see why this would be a problem.

Does the attached work for you?

 

Sales.fp7

[LaRetta]

I have experienced similar failure once a long time ago with Excel when it misinterpreted the data type and the cell in Excel remains empty.  It might help to verify the data types (?).

[comment]

7 minutes ago, LaRetta said:

I have experienced similar failure once a long time ago with Excel when it misinterpreted the data type and the cell in Excel remains empty.

I have encountered many cases of Excel misinterpreting the data type, but I don't recall any of them resulting in the cell remaining empty. Do you have enough details to reproduce this problem?

[fm8443]

@Comment --  
Thanks for the FM file.  It looks logical and exports OK for me.  

Could any of the following be my issue?
My layouts do not use Portals.  My layouts don't have the 2nd Table's field/s on them.  I just do an export from the SALES Table's Table View and add-in the 2nd Table's field in the Export dialog.

@LaRetta/@Comment --

(As described earlier) I tried an alternative approach -- I also can't populate my Real_Product_Name field in the SALES table using "Lookup" so I don't think this is purely an Excel problem.  Suspect both issues are caused by my same underlying problem!

[comment]

Why don't you remove the portal and the related field/s from the layouts and see if it keeps working? Hint: it does.

Another hint: having the portal on the layout of Products and the related field on the layout of Sales enables you to see that the relationship is working, without having to export the data and examine it in Excel.

 

 

Edited September 17, 2016 by comment

[LaRetta]

On 9/17/2016 at 7:25 AM, fm8443 said:

[Note:  SALES and TRANSFORMED Tables are linked (relationship) on PROD_ID in the graph & TRANSFORMED::Real_Product_Name contains return-separated text values]

Why would the Real_Product_Name contain return-separated text values in TRANSFORMED?  And why even have a TRANSFORMED table when you have a Products table?

I suggest that you attach your file.  You can File > Save A Copy As > Clone (no records). We can get you squared away in no time.  :-)

Comment has stepped you through the process really well but I think you've either missed a step or you've manipulated the data a bit too much.