Hi Tom -
This is the multi-level parts breakout problem. We discussed this
on the list way back when and I can't locate the solution; however, there is
one and you might already have it in your archive.
Bernie
----------------------------------------
At 09:09 AM 10/27/2002 +1100, you wrote:
>G'day,
>
>I said:
>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>I am wanting to create a parent child relationship
>between trading entities in the same table so I can
>allow users to report on just one trading entity or
>all under multiple levels of holding companies.
>
>I have created the table TradingEntity and the PK
>col is EntityID. I have just programmatically
>added the col ParentEntityID to the table with a
>FK on ParentEntityID to reference EntityID.
>
>When I try to make another structural change in the
>object manager (delete SYS_ROWVER) I get the error
>message [The referenced table TradingEntity does not
>have a compatible Primary Key.]
>
>Which is the bug, allowing me to programmatically
>create the FK or not allowing me to edit the table
>after it is done?
>
>I know I can do it with a RULE, just interested to
>know the "proper" way to do it in R:BASE.
>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>But I was wrong.
>
>With this:
>
>CREATE TABLE `TradingEntity` +
>(`EntityID` INTEGER NOT NULL +
>('Value for EntityID cannot be null.') , +
> `Org_ID` INTEGER NOT NULL +
>('Value for Org_ID cannot be null.') , +
> `HeaderReqs` TEXT (70) , +
> `EntityName` TEXT (70) , +
> `AddressID` INTEGER , +
> `CNRowIDTel` INTEGER , +
> `CNRowIDFax` INTEGER , +
> `CNRowIDEmail` INTEGER , +
> `CNRowIDURL` INTEGER , +
> `EntityNote` TEXT (200) , +
> `CrntAvailable` TEXT (1) DEFAULT 'Y' NOT NULL , +
> `ParentEntityID` INTEGER , +
> `SYS_ROWVER`= +
> (IFNULL((SYS_ROWVER+1),0,(SYS_ROWVER+1))+
>) INTEGER )
>ALTER TABLE `TradingEntity` ADD PRIMARY KEY +
>(`EntityID` ) +
>('Values for rows in TradingEntity must be unique.',+
> 'Cannot delete - values exist in another table.',+
> 'Cannot update - values exist in another table.')
>AUTONUM `EntityID` IN `TradingEntity` USING +
>4. 1. NONUM
>RULES 'Value must exist in TradingEntity ' +
> FOR `TradingEntity` SUCCEEDS +
> WHERE EXISTS (SELECT EntityID FROM TradingEntity WHERE T+
>radingEntity.EntityID = TradingEntity.ParentEntity+
>ID )
>
>In the browser I cannot get the existing values
>for EntityID accepted into ParentEntityID without
>a rule violation message on exit from the last
>row in the browser.
>
>Any clues?
>
>Warmest regards,
>
>
>Tom Grimshaw
>coy: Just For You Software
>tel: 612 9552 3311
>fax: 612 9566 2164
>mobile: 0414 675 903
>
>post: PO Box 470 Glebe NSW 2037 Australia
>street: 3/66 Wentworth Park Rd Glebe NSW 2037
>
>email: [EMAIL PROTECTED]
>web: www.just4usoftware.com.au
>
>"... the control of impulse -- is the first principle of civilization."--
>Will Durant,
>Pulitzer Prize winning philosopher, writer and historian
>
>the most needed product in the world can be found at
>www.thewaytohappiness.org
>
>This email and any files transmitted with it are confidential to the
>intended recipient and may be privileged. If you have received this email
>inadvertently or you are not the intended recipient, you may not
>disseminate, distribute, copy or in any way rely on it. Further, you should
>notify the sender immediately and delete the email from your computer.
>Whilst we have taken precautions to alert us to the presence of computer
>viruses, we cannot guarantee that this email and any files transmitted with
>it are free from such viruses.
>
>================================================
>TO SEE MESSAGE POSTING GUIDELINES:
>Send a plain text email to [EMAIL PROTECTED]
>In the message body, put just two words: INTRO rbase-l
>================================================
>TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
>In the message body, put just two words: UNSUBSCRIBE rbase-l
>================================================
>TO SEARCH ARCHIVES:
>http://www.mail-archive.com/rbase-l%40sonetmail.com/
>
>================================================
>TO SEE MESSAGE POSTING GUIDELINES:
>Send a plain text email to [EMAIL PROTECTED]
>In the message body, put just two words: INTRO rbase-l
>================================================
>TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
>In the message body, put just two words: UNSUBSCRIBE rbase-l
>================================================
>TO SEARCH ARCHIVES:
>http://www.mail-archive.com/rbase-l%40sonetmail.com/
>
================================================
TO SEE MESSAGE POSTING GUIDELINES:
Send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: INTRO rbase-l
================================================
TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: UNSUBSCRIBE rbase-l
================================================
TO SEARCH ARCHIVES:
http://www.mail-archive.com/rbase-l%40sonetmail.com/
