while trying to upgrade php and suitecrm i pretty much made a lot of damages …
i noticed the missing and not working parts too late and could not recover from the latest backup so i restored an instance from a yearly backup.
i hope i can, and i need help to copy\export all records created and or modified after date 31/12/23 from the live instance and them import them to the resorted instance.
im guessing an sql query export and import will do the job and i need your guidance of how to do it
with the help of chatgpt i got this procedure
what tables do i need to exclude ?
DELIMITER //
CREATE PROCEDURE GetRecordsWithDates()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE tableName VARCHAR(255);
DECLARE columnName VARCHAR(255);
DECLARE columnExists INT DEFAULT 0;
DECLARE cur CURSOR FOR
SELECT table_name, column_name
FROM information_schema.columns
WHERE table_schema = 'suitecrm' -- Replace with your actual database name
AND column_name IN ('date_entered', 'date_modified')
AND table_name NOT IN ('job_queue', 'upgrade_history'); -- Replace with actual table names to exclude
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
-- Temporary table to store results
CREATE TEMPORARY TABLE IF NOT EXISTS result (
table_name VARCHAR(255),
id VARCHAR(255), -- Adjust this data type based on the actual type of 'id' in your source tables
date_value DATETIME
);
OPEN cur;
read_loop: LOOP
FETCH cur INTO tableName, columnName;
IF done THEN
LEAVE read_loop;
END IF;
-- Check if the table has an 'id' column
SET @checkColumnQuery = CONCAT(
'SELECT COUNT(*) INTO @columnExists FROM information_schema.columns WHERE table_schema = ''suitecrm'' AND table_name = ''', tableName, ''' AND column_name = ''id'''
);
PREPARE stmt FROM @checkColumnQuery;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
IF @columnExists > 0 THEN
-- Construct the dynamic SQL query if 'id' column exists
SET @query = CONCAT(
'INSERT INTO result (table_name, id, date_value) ',
'SELECT "', tableName, '", id, ', columnName, ' FROM ', tableName,
' WHERE ', columnName, ' > "2023-12-23"'
);
ELSE
-- Construct the dynamic SQL query if 'id' column does not exist
SET @query = CONCAT(
'INSERT INTO result (table_name, id, date_value) ',
'SELECT "', tableName, '", NULL AS id, ', columnName, ' FROM ', tableName,
' WHERE ', columnName, ' > "2023-12-23"'
);
END IF;
-- Prepare and execute the dynamic query
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- Debug: Display the current contents of the temporary table after each insert
SELECT * FROM result;
END LOOP;
CLOSE cur;
-- Final selection of the results
SELECT * FROM result;
DROP TEMPORARY TABLE result;
END //
DELIMITER ;
I’m just wondering:
Do you need to circumvent the standard reports that you can export and import then?
If you somehow can get the reports working, then you can build your exports with the records that you need. That would usually be my first choice of solution under those circumstances.
Another idea - but that could go either way - install a fresh CRM, copy the broken instance database and replace the fresly installed with your DB dump. Copy the customizations over as well and see where you get with this.
That approach might not work if you have multiple / many customizations.
And if none of those approaches work, I’d still look into making the data retrieval from the DB easier and use standard imports.
You can use the open source version of Metabase (extremely easy), Apache Superset or Redash to build your ‘exports’ in an easy way and then use the standard import.
I’d see writing directly into your DB table only as the very last resort (when the import of the working instance is not feasibile - due to the amount of data to load in a time critical manner e.g.)
Apart from that, you’re SQL statement looks raher complex - it might work in that direction, but I’d seek options to make things easier and less prone to error first.
i didn’t think i can accomplished this with the reports, after reading your comment I tried to do it
but i don’t see any option to filter by create date or modify date.
i don’t know metabase and i guess its a good time to get familiar with .
@BastianHammer do you have any insights on the right way to do it ?
1st - users\employee
2nd - security groups and rolls
3rd …
on my tests i imported some opportunities, and if its linked to not exist account, the import is creating the new account but the created by, and the assigned user are wrong.
the problem with exporting data from the suitecrm gui is the linked content
if the linked content doesn’t exist the system will create it but the assigned user is the one that imported the data.
at the end i did it manually,
i exported complete tables one by one via phpmyadmin
then imported it via phpmyadmin using sql REPLACE statement.
Yes, migrations are usually a lot of work, if there are many modules and records invovled.
The order of importing data is important as well - first users, then accounts, then contacts etc. (or different - depending on your setup) so that the related records can be linked.
