[ic] Layout Editor Auto Populate appears to be broken

[Peter]

IC 5.0.0 from rpms, RedHat Linux 7.3, Postgresql 7.3

I'm currently responsible for switching shopping carts from another 
custom made cart to Interchange.  After converting and uploading the 
data, I figured there's no need to upload to the cat or area tables 
because those could easily be auto-populated from the corresponding data 
in the products table.  When I tried the auto-populate operation, it 
populated the area table correctly, but the cat table was left empty.  I 
tried several times, tried applying changes, exporting, etc, and nothing 
seemed to work.

Eventually I gave up and worked out an sql statement to populate the 
table from the psql backend, but figured it would be good to let you 
know about the problems that I experienced when trying to populate it 
initially.

In case anyone else is having this same problem and is also running 
postgresql for thier DB (mysql and other dbs can do this as well, but 
the commands will differ slightly) here's the commands I used to 
populate the cat table from the backend.  Note that i did not have to 
populate the area table this way because it was already done:


You need to create a temporary sequence to assign unique codes to the 
cat records:
CREATE TEMP SEQUENCE cat_code;

If you mess up, just do the following two commands to clear out the cat 
table and reset the sequence (be aware that the second one will remove 
all the cat table entries):
SELECT setval('cat_code',1,false);
DELETE FROM cat;

Then this is what does the actual populating:
INSERT INTO cat (
	code,
	sel,
	name,
	which_page,
	display_type,
	link_type,
	search)
SELECT
	nextval('cat_code') AS code,
	COALESCE(area.code,'') AS sel,
	products.category AS name,
	'all' AS which_page,
	'name' AS display_type,
	'complex' AS link_type,
	'fi=products\nst=db\nco=yes\n\nsf=prod_group\nse=' ||
		products.prod_group ||
		'\nop=eq\n\nsf=category\nse=' ||
		products.category ||
		'\nop=eq\n\nsf=inactive\nse=1\nop=ne\n\nrf=sku'
		AS search
FROM
	(SELECT
		prod_group,
		category
	FROM
		products
	GROUP BY
		products.prod_group,
		products.category
	HAVING
		category IS NOT NULL
	) AS products
LEFT JOIN area ON area.name=products.prod_group;

Hope this helps someone :-)

Peter