Value List of Related Values

[Scott Lafayette]

I am having a wonderful time right now with a relational value list that works, kind of...

I have a company table, a location table and an order table. Both the Order table and the location table are related to the Company table by the "ContacID" field.

When I create a new order, the ship location field will display a drop down value list of all related values from the location table, based on ContactID.

Well, it works beautifully for all companies that were imported into the database when we went live. What I just found it, however, is that for all companies that are being created in the database now, the value list will only show the FIRST related location. The contactID fields are being created correctly for these locations.

Also, if I add multiple locations for a given company, I will only see the first location pop up on the order. If i go back and delete the first location, the next one in line will then show up in the value list - but only that one.

Does this ring any obvious bells? I can provide more details if necessary, I was just curious if this sounded familiar at all.

Edited October 28, 2008 by Guest

[mr_vodka]

If you have gone to your Locations table and the contactID field have the correct ID data in it, then you may want to try and re-index the field.

Just turn off indexing on that contactID field and then turn it back on.

However, my first guess is that either your value list is using the incorrect relationship or that it is not a true one to many.

[Scott Lafayette]

Thank you - I just tried turning the indexing off and on. This did not work.

When I look at the relationship, it certainly appears to be a one to many to me...

Also, if it is not the correct relationship, why is it working for all of the companies that were imported?

One thing that I don't think that I pointed out, too - if I add locations to the imported companies, those locations will show up accordingly.

Seems like maybe something is funky about how my companies are being created?

I'm still at a loss... I just tried rebuilding the company table's ID index, too. No Luck...

[mr_vodka]

can you post a copy of your file with just a few records?

[Scott Lafayette]

Let me see if I can get it down to a reasonable file size...

[Scott Lafayette]

I just deleted a whole lot of records and got the compressed file down to 6mb. This appears to be way too big to upload to this forum. Are there any other options? Can I ftp it somewhere?

Thanks!

Scott

[mr_vodka]

Try just saving the file as a clone and then zipping them. Forget the data for now.

[Scott Lafayette]

Thank you, again, for the follow up... I am not at the office right now and not on a machine with FM - I will be there tomorrow and will try to do that. I really think that I got most of the data out except for two companies and two orders to show the behavior. I guess that there must be something that I am missing, though...

[Scott Lafayette]

I even learned something new with that - I had never saved a file as a clone before... Here is the file... Credentials are "admin" and "pass". That should get you in full access.

eX-Tracker_Pro_10-28-08v3_Clone.fp7.zip

[Scott Lafayette]

OMG - now it is really driving me nuts... Here are two updates...

I dug up an archived file from 6 months ago - before I started getting in and messing with things. The function seemed to work fine. The only change that I have made since was change the way the value list appeared (I concatenated the location name and street address field - it was originally showing just the location name field - but did not fool with the relationship at all).

This morning, I was trying to troubleshoot again and one of the companies that was nto working last week IS working right now. They were created last week and I was not able to pull up multiple locations on the orders. NOW, I can. I then tried other companies that have been added last week and it is still NOT working.

I thought it could possible have something to do with permissions, so I tried both usernames - same result. I can add and delete locations for this one new company, all of the old companies, but not all of the new companies. I tried adding another new one, no luck - can not pull up the locations.

$#@&*()%$!

if anybody has any insight as to what I am missing, please let me know...

Thanks!

Scott

[Scott Lafayette]

Alright - I think I figured something out...

When the locations are being created, by default, the location name field is being set to "Untitled". My value list is trying to cancatenate "Name" and "Address" - so I think that the value list is seeing the duplicate values in the first field and only showing the first location with that value (only showing unique values from the name field - not treating the concatenated value as a unique value).

This was one of those sloppy loose ends that was left a long time ago when specs changed. It certainly came and bit me in the $ this time. I guess I need to figure something out now.

Thank you for whatever time you have put in to helping me out - I am guessing that my solution makes sense to anybody who has looked at this? If not, please correct me.

Thanks!

Scott