Lookup Address info already entered in same db?

[ambweb]

I have a file I'm working on for a talent agency that receives offers from promoters for their musicians. Each offer has venue information. There are currently over 10000 records in the file and most of the venues they work with are already in there (around 3000 unique names). I tried to do a self join so that when they entered the name of the venue, the address and contact information would automatically enter. The relationship is setup as selfbyvenue, with the name of venue field: VENUE as the link. Allow creation of related records is selected. I then placed the fields from the relationship into the address and contact area. It works like a charm if the venue is already in the file. If it is a new venue, as soon as they enter the address info, it dissappears from the current record and creates a new record with just the venue. They then have to go back into the working record and retype the name which is not ideal.

Is there a way to fix this? Should I be looking up instead of dropping the fields straight in, and does this require creating a new field for the lookup? Any help is greatly appreciated...

[jasonwood]

Let me see if I understand the question.

I think the best thing to do would be to have two separate database files. One for venues and one for offers.

If you really don't want to do that... I think you're right about using a lookup, and I don't think you should be allowing creation of related records.

If I understand you correctly, you want to be able to create a new offer (record), enter the venue name, and if you have used that name before, you want the address to be copied from another record (otherwise you can just manually enter it). So... venue name is your field for the self-join, and all the fields that you want copied should be lookups to themselves through the relationship. Lookup fields work nicely in your situation because if they work, they work, if they don't work, you can type in them without affecting other records. Although I'm not confident that you can look up from the same field in a self-join (I don't see why not).

I really think you should have two files though, this could get messy.

BTW: sounds like you have a lot of data you don't want to lose... before making major changes remember to backup and rename or put on a separate disk where you can't mess with it!

Good luck!

--

Jason Wood

HeyWoody.com

[ambweb]

Jason- Thanks for the reply. Of course my data is backed up and safe, while I may not always be the brightest light on the tree, I've learned that much from experience...

I had some time to play around with this tonight. It seems lookups don't work in a self join, or at leat not as far as I could tell. When I created a new field "VENUE ADDRESS LOOKUP" looking up from the related (self) ADDRESS field, the field in all records was blank... Also found out that in order for the user to be able to enter into a field, I had to keep allow creation of related records checked. So I'm back to square one with a system that almost works... Anyone else have any helpful hints? Thanks in advance

[Fitch]

I think you have the RELATED venue field on your layout, when what you actually want is the CURRENT FILE venue field. You shouldn't need to have allow creation turned on, but that doesn't really matter. Now, the address and other fields can be Lookup fields based on venue, or you can just put the related fields on the layout and not bother with the lookup... just keep in mind that only the FIRST related record will be used to display or lookup related data. That could explain your blank result.

Aha, now reading your first post again it makes sense... you may have the current file venue field on the layout, but the address fields are related... so when you enter an address, for a new venue, it sees no related records, so naturally it creates a new record, that is normal behavior for a relationship. You need to have the CURRENT file address fields there, as lookup fields. Lookups do work in a self join.

[ambweb]

Hey Tom-

If there is a way to use lookups from the same file, then I must be doing it wrong...

Here's how the original file is laid out:

VENUE

VENUE ADDRESS

VENUE CITY / VENUE STATE /VENUE ZIP

VENUE PHONE / VENUE FAX

THe way I set it up had everything but VENUE coming from the related file:

VENUE

:VENUE ADDRESS

::VENUE CITY /::VENUE STATE /::VENUE ZIP

::VENUE PHONE /::VENUE FAX

As mentioned in my initial post, this works unless the VENUE is new at which point it takes the data and puts it in a new place.

Last night, I created six new fields in my current file: VENUE ADDRESS LOOKUP, VENUE CITY LOOKUP, etc... These were set up to lookup from the related file fields, as that was the only way I could get the lookup to work. When I placed these in, all the lookup fields were blank. So am I missing a step here? Do I need calculations as opposed to lookups? Do I need a scriptof some sort to make all this work? I'm getting more confused by the day...

[Fitch]

They'll be blank until you create a new record and enter a venue. For existing records, click in the venue field and choose Relookup from the Records menu. Also keep in mind that it's only going to lookup from the first related record in the file, so if that record has an empty address, then your lookup will also be empty.

I think this will do what you want, however I'm with Jason -- it makes more sense to keep the venues in their own file and avoid all this duplication.