Converting legacy data to PW with foreign keys

[artaylor]

Hi,

I have a project that, based on initial specs, I decided to build in another framework due to the client wanting the ability to regularly import data from an existing manufacturing system. I decided a platform that would allow me to match the imported data to the system would make sense. Since then, the project has morphed (I know, it never happens) and the regular import is unnecessary and so a comprehensive admin is needed. I want to switch to PW but don't want to have to manually link thousands of tables.

I have the import routine working for the main table but I am not sure how to import the support tables and automatically create the page fields for them.

Oh, in case it matter, the links are not autoincrement id fields, but character codes. Eg. Category table - code: TRN, description: Transportation

If someone can point me in the right direction it would be much appreciated.

[BitPoet]

Literally thousands of tables or just thousands of rows/records?

[artaylor]

@BitPoet Thousands of rows in one main table that has a dozen or so support tables that would be linked via a Page field in PW.

 

[BitPoet]

Ah, that reads a lot more doable then

For a dozen tables, I'd probably create the templates and their fields by hand as it is a one-time thing. Perhaps you can name the templates like the old tables for easier coding later. Then create a page field on the main template for every linked table and limit it to the according support table template. Your import routine can focus on creating pages with the correct template and filling in the data. The old id can just go into a dedicated field in the support template, and once all your support table pages have been created, you can use simple selectors like $pages->get("template=oldtablename, keyfield=TRN") to get the correct page to link to when you import the main page/table. Depending on whether you need to display the link codes, you can hide or even drop the field in the support templates.

I'm assuming most support tables will be relatively simple and a template with a title and one extra field for the db id code should be sufficient.

[artaylor]

@BitPoet Thanks for the info.

While I have built a few PW sites, I have not had to go much beyond installing some modules and doing basic PW things. I have not gotten to the level of using the API to add data, etc, so if you don't mind sharing a quick snippet of the "import" routine, I would appreciate it. I don't want to take too much of your time, so please ignore this request if it is a lot of time and energy.

Cheers

[horst]

I try to find some snippets, but be on mobile. 

https://processwire.com/talk/topic/10798-how-to-import-table-of-data-into-processwire/?tab=comments#comment-101620

[horst]

Another one:

https://processwire.com/talk/topic/11047-best-way-of-organising-imported-data/?tab=comments#comment-104700

 

[artaylor]

@horst Thank you. I will give these a read. 

I appreciate the help.

This forum and the PW users are one of PW's biggest assets.