Accessing Data

[mhudd]

I need the ability while working in a record to access data from several other "designated" records in my DB. Is it possible?

[Peter Fenner]

What do you mean by "designated"?

[mhudd]

It's the first record I've entered for a group of records. Its just so happens that the first record has some info on it that I would like to access when I'm working on other records in the DB.

[mhudd]

You know what? That made absolutly no sense at all, even to me.

Let me try again...

I have a series of records in a DB where one the fields has the same name in all the records. however in the first record there is information I would like to look at while working with the other records.

Can I create a new layout that only contains the info from the the first record of a "group" of records with the same field name?

I hope that's a little clearer...

[Peter Fenner]

It sounds as if you only want to access data that exists in one record - am I correct and will it always be this way? - or will you ultimately need to access data from various designated records? Also, how much data and what type of data are you trying to access?

Pete

[mhudd]

9 fields 2 of which are calc fields

[mhudd]

Pete,

I may have 50 records that have the name field name FSBO with the first record having the info I would like to refer to without searching for it.

Then I may have 40 records with the field name ICON again where the first record with the name ICON has info I would like to refer to when I'm in record 28 of the ICON's

[Peter Fenner]

Final question before we find a solution:

Do you need to edit this "master" data from time to time or is it fixed for eternity?

Are the 9 fields of master data you are trying to read going to determine what you enter in the other records with the same field names, or are they going to be exactly the same?

[ February 28, 2002, 02:31 PM: Message edited by: Peter Fenner ]

[mhudd]

Does the data you need to access from different records exist in only ONE record or could their be different data you need to read from different records (same fields with different entries

Same fields with different entries

Also, do you need to edit this "master" data from time to time or is it fixed for eternity?

I need to be able to edit the data

[Peter Fenner]

Are the 9 fields of master data you are trying to read going to determine what you enter in the other records with the same field names, or are they going to be exactly the same?

[mhudd]

I want to use the 9 fields only as references - perhaps to copy and paste the entry from the "designated" into the new record and edit it.

[Peter Fenner]

Okay, I think I have finally got it!

The best thing to do is create another database (db2) where the primary field (which will link to your current database) holds the master name (eg: FSBO). This new database will have the fields with this master data (not the calculations however).

In your current database create a relationship between the field name (where you enter FSBO) and the primary field of db2.

Make the fields in your current database be CALCULATED VALUES from the values from db2 based on this newly created relationship. (set this in define fields > auto-enter)

Therefore as soon as you create a new record and enter FSBO it will automatically bring in this data from db2.

So database 2 holds all your master data for FSBO (1 record), ICON (2nd record) etc.

[mhudd]

Pete,

Does that mean I'll have to got to (db2) to fill in the master date?

[Peter Fenner]

Yes, but you can simply create a button in your current database (db1) that will GO TO RELATED RECORD (the same newly created relationship) in db2.

This is the most effective way. Are you loathe to create another database for this master data or are you really keen to just have the one database?

[mhudd]

I really really would like to keep this one database.

[andygaunt]

You two seem to have a good thing going here, but I hope you don't mind the intrusion.

If, when you create your "MASTER" record, why not set up a field to indicate it as a master. As it is the first record, you could script this with your new button.

then, when creating related records designate a second button to add a related record.

This could then set the master id into a seperate field.

you could then set up a relationship from the main identifier to the group of records with this secondary id field and therefore display the information you want via related fields.

Does this make ANY sense!

[ February 28, 2002, 03:15 PM: Message edited by: Andy Gaunt ]

[mhudd]

Kinda sorta...

[mhudd]

How about a script that goes to the first record of that group and duplicates that record whenever I add a new record to that group.

[Peter Fenner]

Okay then perhaps you would be OK with this:

Create a field called "Master Identifier" for example. Make the field a check box where the value list you create will simply have the entry of "yes" in custom values. (make your field size 14 x 14 pixels - so you won't see the word yes, just the check box)

Now, create a calculation field (c1) like so:

"Name" & "_x_" & "Master Identifier"

If the checkbox is ticked it would produce a result for example of:

FSBO_x_yes

Now create another calculation field (c2) like so:

"Name" & "_x_yes"

This would produce the same result.

Now define a relationship between fields c2 and c1.

Now you need to create a script as follows (this will bring in the master data):

Set Field "Telephone" (for example) as the related field "Telephone". Repeat the set field script step for all the fields you require, except the 2 calculation fields you mentioned earlier.

You would probably want to use the show message script step at the start of the script to verify if the user wants to run the script - in case the script button is accidently clicked and overwrites newly adjusted data.

You can still perform CALCULATED VALUE entries as mentioned before on the same principal

Am I making any sense?

[ February 28, 2002, 03:38 PM: Message edited by: Peter Fenner ]

[mhudd]

Maybe the second DB makes better sense...

[andygaunt]

Michael,

It is true that relational databases can sometimes be easier, but before you do this, download this.

www.albion.co.uk/sample/group.zip

it is a sample of the primary/secondary records.

fairly easy to implement.

Just go to the scripts menu and choose new record. It will ask you if you if you want to create master (primary record) or a secondary record.

Let me know

[ February 28, 2002, 03:54 PM: Message edited by: Andy Gaunt ]

[Peter Fenner]

Defenitely!

It's also really great to be able to access all your "default/master" entries in 1 database - and it's not really such a hassle.

Well, it's been fun - 4 cups of coffee and about a box of Camel later I think I should head to bed (it's 2am in Cape Town, South Africa)

I hope you get what you wan't from your database (Or should I say databases) - Good Luck!

Cheers for Now! - Pete

[andygaunt]

Peter,

It has been fun.

switch to lights and hopefully the coffee won't keep your head buzzing.

Midnight here in the UK so I will call it a night as well.

Michael, try the sample and I will check first thing to see if it worked out for you.

[Peter Fenner]

Andy,

Your profile suggest some experience - what did you think of my one database solution - I'm sure my logic is correct, but I do worry I over-complicate the issue sometimes. I'll look at the web link you suggested to Michael also.

PS: You see our SA cricket team is playing against Australia as if we are the English Team!!!

[ February 28, 2002, 04:04 PM: Message edited by: Peter Fenner ]

[mhudd]

I'm not quite following how to use the concept...

[Peter Fenner]

Michael,

I will compile a much clearer explanation over the weekend, and send you an e-mail by

[Fitch]

Greetings from Mr. Butt-inski!

It sounds to me like all you need is a simple self-relation. You have a "tag" field where you might enter "FSBO" or "ICON," right? OK, make a tag::tag relation. Now put the related fields you want to see on your layout. If you put the fields in a portal, you'll see data from all the related records. If you don't put them in a portal, you'll see data from the first related record. Isn't that what you wanted to see?

[andygaunt]

Fitch,

Exactly. See my earlier post, or the sample file.

I just didn't want to use the "TAG" field as you never know, this might not be unique.

As you say, P.O.L.R

[ March 01, 2002, 01:14 AM: Message edited by: Andy Gaunt ]

[mhudd]

Thank you everybody for your time and efforts.

Problem solved!