Remove occurrences of a value in checkbox field

[Kent Searight]

I have a checkbox field in TableA that shows a value list whose values are derived from a field in TableB. It's imperative that all values in the checkbox field exist in the current value list. All is fine until I delete a record in the TableB, thus deleting a value from the value list. If that value that was just deleted was one of the values checked in the checkbox field, it ends up being a hidden "orphaned" value in the checkbox field in TableA.

My question is: What is the best way to get rid of the orphan values in the checkbox field? The table containing the checkbox field will hold 10's of thousands of records, so I want to make sure I don't end up doing it in some "clunky", time-consuming manner. Is looping through all the records and performing a replace in the checkbox field in TableA the way to do this? If so, how do I return the state of TableA to its original found set after deleting the orphan values?

Thanks in advance for any help you may offer!

[T-Square]

I'd recommend getting rid of this structure, for just this reason. The field in TableA ends up just being a text field with the values from TableB entered. There's no truly elegant way to eliminate the orphans (it can be done using scripts, but it's not elegant). Moreover, it's harder to search and display the data in this field because of its structure.

Instead of storing your TableB information in an embedded TableA field, create what is known as a *join table*. This join table (say "Join_A_B") at its most basic has 2 fields:

ID_TableA - links to TableA unique ID

ID_TableB - links to TableB unique ID

Create 2 relationships:

TableA::ID <=> Join_A_B::ID_TableA

TableB::ID <=> Join_A_B::ID_TableB

Each relationship should have the checkbox for "Delete records in this file" (or whatever it says) checked for the Join_A_B table, which ensures that if you delete either a TableA or TableB record, the children of those records will be deleted (thus fixing your orphan problem).

To manage your categories, replace your checkbox list with a portal. The portal is built using the relationship from TableA to Join_A_B. The portal should allow creation and deletion of records, and you can create a dropdown from TableB records that will automatically populate your Join file field. I am told that you can actually create a dropdown that will progressively remove entries from the dropdown so that you avoid duplicate entries in the join file, but I haven't done that myself.

Removal of single entries is as easy as putting a button on the portal that deletes the current portal row; I write a script that checks whether the record has data and if so, delete the portal row. To be a little slicker, I create a calc field that is simply:

If[Not(IsEmpty(Join_A_B::ID_TableA)), "X", "")

which I put on the portal, and I assign the script to that. This way, there's a big red X next to each entry that I can click to delete the current row. Your script can prompt the user or not...

[Kent Searight]

Hi T-Square,

I originally had it set up that way (many-to-many)and then opted to do it with a checkbox. A little more info on the history of that can be found at Post #175680 where I discussed something similar with someone who was trying to do it similarly to the way you've suggested.

I've already written a script that eliminates the orphans and it works well except...I have yet to test it in a database with many thousands of records and I was concerned about speed...which led me to make my original post. But, I think I just found a good solution to the speed issue also. Instead of looping through every record and deleting the value, the script creates a Found Set of only records that contain the value I want to eliminate. This should considerably improve performance (I hope).

BTW, I've figured out how to restore the original found set I asked about in the original post, so no one need respond to that.

Now the only thing I can't figure out how to do is eliminate any leading character returns. I know I've done it before and it's easy but I'm experiencing brain-fry right now. Can anyone help???

Thanks, T-Square, I appreciate your advice.

I'll post the script when all is done so that others can see how I did this.

[Kent Searight]

I just found a nice solution on how to trim leading and trailing carriage returns (as I'd requested help for in previous post). It is a recursive function and can be found at Post #174759.