How to find relation in Sage 50 Databse v22 in mysql(Already Converted to mysql)

Question

I am new to mysql and accounting, however, I have converted Sage 50 V22 database table to mysql via ODBC and but unable to find relation. There is no primary key selected.

My project is to create similar application like Sage 50 but in PHP and MySQL

My question is shall I manually create relation or is there any way to find relation by command?

Thanks in Advance

for your reference I include some table .........................

# Table structure for table 'LETTER_TYPE'
#

DROP TABLE IF EXISTS `LETTER_TYPE` CASCADE; 
CREATE TABLE `LETTER_TYPE` (
  `UNIQUE_ID` INT,
  `LETTER_TYPE` SMALLINT,
  `DESCRIPTION` VARCHAR(30) CHARACTER SET utf8,
  `RECORD_CREATE_DATE` DATETIME,
  `RECORD_MODIFY_DATE` DATETIME,
  `RECORD_DELETED` SMALLINT
) ENGINE=MyISAM;

#
# Dumping data for table 'LETTER_TYPE'
#

LOCK TABLES `LETTER_TYPE` WRITE;
INSERT IGNORE INTO `LETTER_TYPE`(`UNIQUE_ID`, `LETTER_TYPE`, `DE SCRIPTION`, `RECORD_CREATE_DATE`, `RECORD_MODIFY_DATE`, `RECORD_DELETED`) VALUES(1, 1, 'Reminder', '2013-08-10 21:31:50', '2013-08-10 21:31:50', 0), (2, 2, 'Warning', '2013-08-10 21:31:50', '2013-08-10 21:31:50', 0), (3, 3, 'Legal threat', '2013-08-10 21:31:50', '2013-08-10 21:31:50', 0), (4, 4, 'Statements', '2013-08-10 21:31:50', '2013-08-10 21:31:50', 0), (5, 5, 'Orders/Quotes', '2013-08-10 21:31:50', '2013-08-10 21:31:50', 0), (6, 6, 'Invoice/Credits', '2013-08-10 21:31:50', '2013-08-10 21:31:50', 0), (7, 7, 'GDN/GRN', '2013-08-10 21:31:50', '2013-08-10 21:31:50', 0), (8, 8, 'Remittances', '2013-08-10 21:31:50', '2013-08-10 21:31:50', 0), (9, 9, 'DefaultLetter', '2013-08-10 21:31:50', '2013-08-10 21:31:50', 0);
UNLOCK TABLES;
ALTER TABLE `AUDIT_HEADER` MODIFY `DATE` DATE;
ALTER TABLE `AUDIT_HEADER` MODIFY `DATE_ENTERED` DATE;
ALTER TABLE `AUDIT_HEADER` MODIFY `RECORD_CREATE_DATE` DATE;
ALTER TABLE `AUDIT_JOURNAL` MODIFY `DATE` DATE;
ALTER TABLE `AUDIT_JOURNAL` MODIFY `DATE_ENTERED` DATE;
ALTER TABLE `AUDIT_SPLIT` MODIFY `DATE` DATE;
ALTER TABLE `AUDIT_SPLIT` MODIFY `DATE_ENTERED` DATE;
ALTER TABLE `AUDIT_USAGE` MODIFY `DATE` DATE;
ALTER TABLE `AUDIT_VAT` MODIFY `DATE` DATE;
ALTER TABLE `AUDIT_VAT` MODIFY `DATE_ENTERED` DATE;
ALTER TABLE `BANK_STATEMENT` MODIFY `STATEMENT_DATE` DATE;
ALTER TABLE `BANK_STATEMENT` MODIFY `RECONCILIATION_DATE` DATE;
ALTER TABLE `CONTACT_HISTORY` MODIFY `CONTACT_DATE` DATE;
ALTER TABLE `CONTACT_HISTORY` MODIFY `START` DATE;
ALTER TABLE `CONTACT_HISTORY` MODIFY `END` DATE;
ALTER TABLE `CONTACT_HISTORY` MODIFY `DURATION` DATE;
ALTER TABLE `CONTACT_HISTORY` MODIFY `FOLLOW_UP_TIME` DATE;
ALTER TABLE `GRN_ITEM` MODIFY `DATE` DATE;   
ALTER TABLE `INVOICE` MODIFY `INVOICE_DATE` DATE;

#     
...........................

# Table structure for table 'PERIOD'
#

DROP TABLE IF EXISTS `PERIOD` CASCADE;
CREATE TABLE `PERIOD` (
  `PERIOD` SMALLINT,
  `DESCRIPTION` VARCHAR(29) CHARACTER SET utf8
) ENGINE=MyISAM;

#
# Dumping data for table 'PERIOD'
#

LOCK TABLES `PERIOD` WRITE;
INSERT IGNORE INTO `PERIOD`(`PERIOD`, `DESCRIPTION`) VALUES(0, 'Brought forward'), (1, 'Month 1, June 2015'), (2, 'Month 2, July 2015'), (3, 'Month 3, August 2015'), (4, 'Month 4, September 2015'), (5, 'Month 5, October 2015'), (6, 'Month 6, November 2015'), (7, 'Month 7, December 2015'), (8, 'Month 8, January 2016'), (9, 'Month 9, February 2016'), (10, 'Month 10, March 2016'), (11, 'Month 11, April 2016'), (12, 'Month 12, May 2016'), (13, 'Complete (all transactions)');
UNLOCK TABLES;
ALTER TABLE `AUDIT_HEADER` MODIFY `DATE` DATE;
ALTER TABLE `AUDIT_HEADER` MODIFY `DATE_ENTERED` DATE;
ALTER TABLE `AUDIT_HEADER` MODIFY `RECORD_CREATE_DATE` DATE;
ALTER TABLE `AUDIT_JOURNAL` MODIFY `DATE` DATE;
ALTER TABLE `AUDIT_JOURNAL` MODIFY `DATE_ENTERED` DATE;
ALTER TABLE `AUDIT_SPLIT` MODIFY `DATE` DATE;
ALTER TABLE `AUDIT_SPLIT` MODIFY `DATE_ENTERED` DATE;
ALTER TABLE `AUDIT_USAGE` MODIFY `DATE` DATE;
ALTER TABLE `AUDIT_VAT` MODIFY `DATE` DATE;
ALTER TABLE `AUDIT_VAT` MODIFY `DATE_ENTERED` DATE;
ALTER TABLE `BANK_STATEMENT` MODIFY `STATEMENT_DATE` DATE;
ALTER TABLE `BANK_STATEMENT` MODIFY `RECONCILIATION_DATE` DATE;
ALTER TABLE `CONTACT_HISTORY` MODIFY `CONTACT_DATE` DATE;
ALTER TABLE `CONTACT_HISTORY` MODIFY `START` DATE;
ALTER TABLE `CONTACT_HISTORY` MODIFY `END` DATE;
ALTER TABLE `CONTACT_HISTORY` MODIFY `DURATION` DATE;
ALTER TABLE `CONTACT_HISTORY` MODIFY `FOLLOW_UP_TIME` DATE;
ALTER TABLE `GRN_ITEM` MODIFY `DATE` DATE;
ALTER TABLE `INVOICE` MODIFY `INVOICE_DATE` DATE;

#

score 0 · Answer 1 · answered Jul 18 '17 at 21:10

You have two options to solve your problem. First you can use the sage data dictionary and add all relations manually or my pick, you can connect directly using mysql to the sage 50 database and get a full dump of it.

PS: in the backend Sage uses mysql as database.

How to find relation in Sage 50 Databse v22 in mysql(Already Converted to mysql)

1 Answers1