Creating Relationships on the Fly in a Script

[crayfish]

Is there a way to create a relationship between tables on the fly in a script so that you don't need to create a table occurrence.  Any plugins?  Our relationship graph has 300+ table occurrences (anchor buoy), and we'd prefer not having to create table occurrences unless they are going to get a lot of usage, particularly on maintenance scripts that will be only used once.  

 

I know we can create a table occurrence and then delete it, but there's a tendency to forget to delete it...

 

 

[bcooney]

Create a TO and then delete it? I'm curious as to that use case.

 

Have you looked at Execute SQL ( )?

[comment]

Is there a way to create a relationship between tables on the fly in a script

 

 No, it's not possible to script any changes to a file's schema.

 

 

maintenance scripts that will be only used once.

 

That's a very strange-sounding animal. Why would you script something that only needs to be done once?

 

 

Our relationship graph has 300+ table occurrences

 

That's even stranger.

 

 

it seems to me you could use another file with no tables of its own, only references to the "real" file's tables. Then when you need to perform a one-time operation on the "real" file, use this "control" file (or one of several such files) to create the necessary scripts and relationships, leaving your "real" file free of clutter that's not required in day-to-day operations.

[crayfish]

Thanks guys!

 

BCooney: I'll look into ExecuteSQL.

 

Comment: The control file is also a good suggestion.

 

BTW, Our database has 57 base tables, and also accesses 10 additional tables over ODBC.  When you're using Anchor Buoy, it means relationships will appear more than once, so getting to 300+ isn't very hard.

 

 

1-time use scripts aren't needed very often, but they do come up, particularly when redesigning the overall database structure

[comment]

When you're using Anchor Buoy, ...

 

To me, that's like saying "when you're freezing in hell, ..."

[Helpful Harry]

You cannot have a Script which actually creates a Relationship, but you can have a pre-set-up "dummy" / multi-use Relationship using fields which a Script can then change the data values on both ends to establish the wanted relational links.

For example, if you have a database with two tables, you can create some fields in each table which are only used for the alterable Relationship (A_Parent in Table A and B_Child in Table), and a Relationship that links the two tables using these fields. (Depending on the needs, A_Parent might be a Global Field.)

Then,

• if today you need a Relationship that matches gender, you can have the Script (or do it manually) copy the data from the real Gender field into A_Parent and B_Child for each record in the appropriate tables.
• if next week you need a Relationship that matches eye colour, you can have a Script copy the data from the real EyeColour field into A_Parent and B_Child for each record in the appropriate tables.
• etc.

One Relationship, two fields, and it can be used to create different Relationships "on the fly" as needed.

There is one problem - this is not going to work for a multi-user database where each user needs to run their own "on the fly" Relationships, but it will work for a single-user database or a multi-user database where only one "Admin" person needs this functionality.

For a limited number of multiple users you could create a dummy Relationship / fields for each user, but it would quickly get messy and unwieldy.

It will also be quite slow if you are changing the field data for bazillions of records or using a slow computer / connection