How to avoid creating duplicate records in a related database?

[jwparker]

I want to use a list of images from one database as a value list in another database. The list in the first db needs to be able to display more than a single image, so I have the two linked via a join table, and use a portal to display the image list. My problem is that I don't want to add records to the image list each time I use one of the values in the portal. I can't figure out how to JUST use the values, and not create a new record each time I choose one of the images.

Using the image database as a simple value list in a repeating table doesn't work, as choosing one value populates the other values with the same name as many times as it is chosen.

More detail on the database setup:

The first database is a list of shows. Each show needs to display and store data on which images were sent for judging. The image list changes over time, and is also the basis for my inventory, so one instance of each image is all that should be in the image list.

I've set up the show database to auto-create a primary key each time a new year is added. The image database also creates a key for each image as images are added to the database. These two keys are used to link the two databases in a join table. The relationship coming from the show side allows creation of new records in the join table; the join between the image list and the join table allows creation of new records in the image list. My problem is that they won't link if I turn OFF the create new record in the image list, but I don't want to create a new record in the image list, just use the names.

Is there a better approach to this? The image list works really well as a drop down to auto fill. Each show needs to be able to add a variable number of images that were sent for judging in the portal field.

The attached sample file shows how the first show record has added three duplicate image records to the image db -- any suggestions appreciated here. I know I'm doing something wrong, but can't figure it out.

TIA Jim

ShowsImages.zip

[comment]

See if these help:

http://www.fmforums.com/forum/showtopic.php?tid/185912/post/246743/#246743

http://www.fmforums.com/forum/showtopic.php?tid/185835/post/246150/#246150

[jwparker]

I'm not sure that this is what I'm looking for. A show can have multiple images assigned to it; an image can be used for more than one show. I'm going to have to look at the dwindling list example more closely to see -- thanks!

One question -- the dwlist example uses three tables in one database -- can a linked database be used in the same fashion as the organization table?

And I don't understand the relationship between the cMyOrganizationIDs calc field and the unaffiliated organization table occurence yet. What's the function of that -- not to be stupid, but that looks like the crux of the matter.

Thanks for your help -- this might be the answer, I just don't get it yet.

[comment]

That's exactly the situation between Contacts and Organizations in the demo file of the second link.

[jwparker]

What would be the syntax to use a file reference instead of an internal table in the calculated field:

// FOR VERSION 8.5:

List ( Affiliations::OrganizationID ) & "¶ "

// FOR PREVIOUS VERSIONS:

// ValueListItems ( Get (FileName) ; "MyOrganizationsIDs" ) & ¶

Just changing the names and labels in the three tables in the example to conform to my data works, but I can't seem to make the example work with an external file reference to the image list.

It's important to me to keep the image database separate as that will form the basis for a much larger inventory database. the images should be consistent in both databases, as I'd like to be able to edit that list once, not twice for every addition or change.

[comment]

A calculation does not care about file references. You need to put an occurrence of the table in the other file onto your relationships graph. Once you have that, you define relationships to it and refer to it in calculations just as if it were a local table.

[jwparker]

Ok, thanks. Wasn't sure if the syntax needed to change. I did put a TO into the relationship diagram and linked to it, but that file did not function properly. Somewhere, something is kerspuggled. At least syntax is not at fault, thanks.

[jwparker]

Your help got me into the ballpark -- I really appreciate it. I was able to reference the secondary file, but I'm getting some flaky behavior with the simple script that pops the ImageID in the Show_Yr layout. Clicking on the button in the portal only results in a blank edit field -- I can type an ImageID code and tab out of the field, and then it will populate correctly, but it won't do it on it's own.

I have the join table set to allow record creation, but I'm not sure that the button is triggering it properly.

The other odd behavior is that once I get the drop down menu to populate correctly, it will still add multiple instances of the same image to the submitted images layout as individual records, which it shouldn't do, according to the example.

I've attached the second revision of the files -- I hate to be such a stupido! But getting this small part working has cost me the better part of two days and I'm still nowhere near done! Oh well!

Thanks again --

ShowsImages_2.zip

[comment]

A couple of points:

1. Either change the ImageID field to be Text, both in the images table and in the join table, or make it numeric only;

2. The calc field cSubmittedImagesIDs should have "Do not evaluate if..." turned OFF;

3. You need to commit a record before going to the next portal row. If that's not convenient, consider using a portal of unsubmitted images (using the same relationship), and creating new submissions by clicking on an unsubmitted image - kind of transferring from one portal to another.

[jwparker]

That sounds right on! If I can figure out how to get the record to commit (a deep problem affecting many people in today's world ;-) I may be there. I do appreciate your assistance. I'll give this the old college try tomorrow and see what other problems crop up. Thanks again.

jim

[comment]

You just need to click somewhere off the portal. Or press enter. Making the field non-enterable might also help a bit, and so can disabling auto-tabbing to next field/s. Overall, it's not the best UI you can think of - that's why I suggested a slicker (IMHO) alternative.

You should also validate for uniqueness - follow the first link in my first post.

[jwparker]

You just need to click somewhere off the portal. Or press enter. Making the field non-enterable might also help a bit, and so can disabling auto-tabbing to next field/s. Overall, it's not the best UI you can think of - that's why I suggested a slicker (IMHO) alternative.

You should also validate for uniqueness - follow the first link in my first post.

It COULD be done with value lists, but as Fenton says, it would require a rather complicated structure of relationship - esp. since you want to make the selection available from both sides. A simpler solution, IMHO, would be to script the selection, and use a new window to present the available options.

For example, to select an employee:

Freeze window;

Open a new window;

Go to a list layout of Employees;

Find employees with no assigned vehicle (search the related Vehicles::EmployeeID field);

Pause script

The user selects the record by clicking on a button that resumes the script:

Set a variable to the selected EmployeeID;

Close the window;

Set the EmployeeID field to the variable;

Commit Records

It does work, now, and as you point out, the UI is not the slickest. Are you referring to the method where you discussed popping a window via scripting and selecting from there? It makes sense, to a point, but wouldn't I have the same issue with multiple lines in a portal in a separate window? With a list layout, wouldn't you have to open that window a number of times = to the number of choices needed?

The original db had checkboxes, but the number of choices outgrew that solution, and it was impossible to keep in sync with the images db. This works, for now, as it's only me using the db.

As for validation, the combinations are often NOT unique -- one of my reasons for tracking this data is to find reocurrencs of the same combination. Are you saying that each submission record should be unique, or to validate the choices as unique? Not sure I understood the reason for unique validation.

You've been a great help -- glad I stumbled in here.

[comment]

I wasn't referring to popping up a new window with a list (or a portal), but that could work, too. I think it's less suitable for multiple repeated choices - because as you say, you would need to pop it for each selection, just like a drop-down. You could of course make it accept multiple selections, but that would be rather unusual behavior: one would expect multiple selections to go into the same destination.

What I meant was a static portal showing all the yet unselected images (same as your value list does now), placed side-by-side with your current portal of selected images. A --> button in the left portal creates a new join record for that image. A <-- button in the right portal deletes the join record. For the user, it looks like they are shifting data over from one side to another. Lots of fun.

Are you saying that each submission record should be unique, or to validate the choices as unique?

I assumed you wanted each image to be submitted only once for each parent record. If that is so (and that's a business decision, nothing to do with Filemaker), then you should validate for uniqueness of the combination ImageID|ParentID, as an extra measure. One shouldn't rely on UI alone to ensure data integrity.