Many to many lists

[miplatt]

In our database, in many places where we have many to many links, somehow information is not being preserved.

For example, we build and save a list of entities. When we open the list again to add more entities by using the filter popup, and we use the filters to locate the entities we would like to add, the resulting list leaves us with only those entities fitting the criteria of the filters and NOT the originally selected entities.

As a result, we are unable to create and modify lists in an effective manner.

I apologize if the explanation is not clear enough. Moshe can explain more accurately.

Please advise.

[ZevToledano]

I think I understood. You are using the Advanced Search feature to populate a many-to-many list instead of the basic/default options because you need to be able to filter the list by fields other than the name field.

This behaviour is a known issue. This is one of the reasons why Advanced Search is optional. See, the problem is, that this feature uses a database query to populate the list. So if your filters exclude your previously selected entries, these entries will not be displayed in the list and therefore will not be added to the many-to-many field when you press OK. This is only a problem when you keep selecting different filters every time you want to the edit the same field for the same record.

If you must have the advanced filters and can't go back to using the basic search setting, then I am at a loss to come up with a way to make this work. Perhaps you could suggest a solution.

If we keep the previous entries no matter what filters you set, then that can cause problems with users that want to remove previous entries and they won't see them when they open the new list. I can't show the user a list that is incomplete and preserve entries behind the scenes without him seeing them. This is just asking for trouble.

If I always show the entries regardless of his filters then two things will happen: The list will display items that are not appropriate to his filters, and worse, the entries will not have any values in the columns and will appear as blank entries because they were not included in the database query.

Any suggestions?

[ZevToledano]

P.S. This is not really a solution but, in many cases, you may be able to find the additional records you need using sorting instead of filtering.

A possible solution is to ask the user what to do. I.e. if the program detects that some records are missing after the filter, ask the user whether to merge into existing values or to throw them away. How does this sound to you?

[miplatt]

That does sound like a reasonable solution--this would allow the user to determine each time whether to merge with or eliminate the previous ones selected, which would account for various different situations (sometimes the user might want to merge and sometimes not).

I will keep thinking about it.

[miplatt]

Previously, we discussed the problematic issue of adding to a query.

Once we choose the initial entities or filters in a query, is there a way to fix that as the list such that the query could then be disassociated with the filters that enabled creation of the list. Then, could we remove the entities that are not relevant? In other words, not add but subtract...

This would allow us to generate a query and then refine it manually by removing certain companies--without having to wade through the entire list of companies in existence (even if the selected entities appear at the top).

For example, using advanced filters I find all companies dealing with environmental applications to be contacted. Once I have that list, I would want to look through the list and take out any companies that are not exactly relevant to the project at hand. I would then want to contact the resulting list or send it to a colleague to contact.

[ZevToledano]

I am sorry but I don't understand: "the query could then be disassociated with the filters that enabled creation of the list".

What does "refine it manually" mean and why can't you simply refine it manually by unchecking the ones you don't want?

Also, why not simply combine multiple filters to find the exact ones you need in the first place?

[miplatt]

I may be probably missing something and making things more complicated. I will try to explain further:

If we know in advance, certainly we can utilize the filters to reach a desired list.

However, for many of our projects, we must start with an overall group of companies selected using filters. Then, as we do more research and/or contact them, we find out that certain companies are not relevant or interested. Ideally, we would then simply want to remove them from the list, as opposed to starting the list each time from scratch.

As of now, to refine the list, I select all of the desired ones and ask for clay to open these items as a query, thus giving me the refined list. Then to save it, I send it in a message to myself (adding it to the toolbar can be cumbersome as these "saves" need to happen quite often and then must be removed). In many cases, each time I call and need to eliminate a company, I need to repeat this process.

When you say "unchecking the ones you don't want," I am not sure as to what you are referring.

[ZevToledano]

You posted this under a topic of many-to-many lists. When I say unchecking the ones you don't want, I assume we are still talking about this. Now it sounds like you are talking about a list query without using a many-to-many field.

There are several ways to manage a list. One is by using a many-to-many field inside another encapsulating record. Another is to use another custom table with links to the contact list, and create/delete records when you want to add/remove contacts. This can be done easily using the Create Item menus feature. Alternatively, activate/inactivate them depending on whether you need historical info and how you work with lists. Or, if you only have a single recipient list at any given time, you could simply flag/unflag the contacts themselves and simply open a list that filters the unflagged ones out. Etc.

For example, if this were a lead campaign, you would have a campaign record and there would be links between this campaign and the contacts for that campaign, either through a many-to-many field or through an intermediary table.

[miplatt]

Thanks for these helpful ideas.

We currently have set up a many-to-many field inside another encapsulating record entitled Mailing List (I think this was the original subject of this conversation). Through this, we can uncheck companies as you have suggested.

A current drawback is that when we open the many-to-many field, it brings not only the selected entities, but ALL of the entities in existence with the selected ones at the top with checks and not necessarily in alphabetical order (trying to put them in alpha order ends up with the checked ones spread throughout the long list of all entities).

Without limiting clay's search/filter abilities in other contexts, is there a way to have only the selected entities appear? Then when we uncheck additional ones and save the field and open it again, the ALL of the unselected would not be there?

[ZevToledano]

The field edit button is for editing the field which is why it displays ALL records. You can display only the currently selected ones using a separate list. E.g. add a custom list tab to the form. But then you can't uncheck items from that list. To uncheck you need to edit the many-to-many field.

Alternatively you use the intermediate custom table method and set it up for easy creating of new records, and you can't use checkboxes there obviously but then you can simply delete items from the list.