I have inherited a database that in my opinion was designed very badly, and
now I am having a hard time wrapping my head around what I need to do so
that dabo and the mssql database play nice together.
I have 3 tables where I have to pull fields into table one from tables 2 &
3. Problem is, the fields I need to pull in all have the same names. I have
tables 2 (Cities) and table 3 (ProvinceState) playing nice together but I
can't get table 1 into the mix as well.
TABLE 1 (Companies)
self.DataStructure = (
("CompanyID", "I", True, "dbo.Companies", "CompanyID"),
("CompanyName", "C", False, "dbo.Companies", "CompanyName"),
("Address", "C", False, "dbo.Companies", "Address"),
("City", "C", False, "dbo.Companies", "City"),
############################ Needs to be filled from Cities Table
("CityID", "I", False, "dbo.Companies", "CityID"),
########################### A dropdown to pull cities Choices & Keys from
Cities Table
("ProvinceState", "C", False, "dbo.ProvinceState.",
"ProvinceState"), ########## Needs to be filled from Cities table as part
of CityID
("Country", "C", False, "dbo.ProvinceState", "Country"),
##################### Needs to be filled from ??? to match the value of
CityID in cities table
("PostalZIP", "C", False, "dbo.Companies", "PostalZIP"),
("Phone", "C", False, "dbo.Companies", "Phone"),
("Fax", "C", False, "dbo.Companies", "Fax"),
("Website", "C", False, "dbo.Companies", "Website"),
("InactiveCompany", "I", False, "dbo.Companies",
"InactiveCompany"),
("CompanyActivationDate", "T", False, "dbo.Companies",
"CompanyActivationDate"),
("CompanyInactivationDate", "T", False, "dbo.Companies",
"CompanyInactivationDate"),
("CompanyCreator", "C", False, "dbo.Companies",
"CompanyCreator"),
("ParentCompanyID", "I", False, "dbo.Companies",
"ParentCompanyID"),
("CompanyRating", "C", False, "dbo.Companies",
"CompanyRating"),
)
TABLE 2 (Cities)
self.DataStructure = (
("CityID", "I", True, "dbo.Cities", "CityID"),
("CityName", "C", False, "dbo.Cities", "CityName"),
("ProvinceState", "C", False, "dbo.Cities",
"ProvinceState"),
("Country", "C", False, "dbo.ProvinceState", "Country"),
################## Pulled In from Table 3 with addJoin and addField
)
TABLE 3 (ProvinceState)
self.DataStructure = (
("ProvinceState", "C", True, "dbo.ProvinceState",
"ProvinceState"),
("Country", "C", False, "dbo.ProvinceState", "Country"),
)
Again, I don't have the option of changing the tables and not matter what I
do I can't seem to get it right. I either end up with a Ambiguous field
error or I get the values that were previously entered into the Companies
table when it was stand alone (more correct to say before it relied on the
Cities table)
I know this isn't really a dabo specific question, but if anyone can help
me wrap my head around this it would be greatly appreciated.
