 | Matt Warner 10/18/2007 6:08:26 PM We have several places in our database where user creates detailed records for a master record. For example, when we visit a customer, we log the meeting by creating a record in a Visits table. Then we create records in another table linking each customer contact that was present from the Contacts table to the correct record in the Visits table. So adding these records in the linking table is done using the Record Picker, and must be done one at a time (presumably because each link between contact and visit is a separate record in the table). However, with the nice options available in the record picker, I can see in one view all of the contacts for a particular location and you want (very badly) to hold down the CTRL key and select all of the people that were there at one time. Instead, I have to use the record picker once for each contact that was there which is quite time consuming when there were 10 people at a meeting.
I think it would be nice to add a property to a column called "allow multiple selections" or something similar. Then in record picker have the ability to select multiple (maybe CTRL key). I guess you would also need some format for this to be shown (or entered manually) in edit mode. Maybe something like "item1 | item2 | item3" and then create one record in the table for each selection with all the rest of the column values being the same for each record.
This ability might need to be limited to one column per table?
|
 | Rick Cogley 9/2/2010 4:46:19 PM This would help usability and user adoption in a huge way. Yes, please!
|
 | Liquid Rapid 9/2/2010 6:34:08 PM I think this is a *great* idea.
I agree with what Kirill said here about the usability of the many-to-many relations via details views: It can be inconvenient for users.
The largest task when I create many-to-many relations in applications seems to be designing and installing the interface. With a ready-made masquerade column capability like this, time spent producing many-to-many relations would be cut for developers.
When I produce many-to-many relations, I use the special Custom Action buttons functionality to rename the standard "New" and "Delete" buttons to "Add" and "Remove". I think Add and Remove match the way users think of the functionality better than New and Delete. The Del button especially makes me think that some data will be lost from the system when I click it. I vote the buttons are named Add and Remove, or that we have the capability to rename those buttons in the Multireference Column configuration.
I'm really looking forward to having this functionality available.
|
 | Kirill Bondar 9/3/2010 5:41:12 AM |
| Kirill Bondar 9/3/2010 5:54:10 AM Del button is now called Remove
|
 | Rick Cogley 9/3/2010 6:34:20 AM Fantastic way to test, guys. Wow, I like this feature, and it is great that the link table gets made automatically.
But a couple of points -
I tried copying my main app and testing it in the copy, then adding one of these columns, when I try to access the Edit mode of the record that should have the new multi-reference dropdown, the system says "internal error" and that it notified you.
Also, we have a lot of these m:m link tables already hand built. How can we convert? We have cases in which we made various columns in addition to the standard ID columns in the link tables, too.
Finally: since it automatically makes the link tables, can we add columns and so on, to those without causing trouble?
Thanks again and I sincerely hope we can use it in production soon.
Regards,
Rick C
|
| Kirill Bondar 9/3/2010 9:22:20 AM The error is fixed.
So far there is no way to auto-convert existing link tables - you can let multiref column to create link table anew and export/import the data from existing table.
You can extend link tables with extra columns, just remember to provide the default values for columns marked as required.
|
| Rick Cogley 9/3/2010 9:37:02 AM Thanks Kirill. How long should the testing of this take before it can be used in production?
|
| Kirill Bondar 9/3/2010 10:01:59 AM Multireference column is the way to provide more convenient viewing editing facility for m:m relations. In fact all the column needs to know to operate is two relationships - from "left" table to "link" table and from "right" table to "link" table. Even if we decide to drop it (unlikely) you'll still be able to edit the data the way you do it now.
Current implementation is limited to text keys only and keys should not contain characters ",", "|" and "~". There also some problems with calculating the UI based on rights set on link table. As for the rest it is very close to a release stage.
|
 | beo 9/3/2010 10:14:34 AM It is possible to make multi-selections (recordpicker mode) to achieve this objective?
|
| Rick Cogley 9/4/2010 1:58:18 AM Thanks Kirill for the bug fix. I confirm it does indeed work for us.
|
 | Philipp Matuschka (MMB) 9/4/2010 5:38:29 AM Hi
I just posted a question about an hour ago, to which this feature is obviously the answer. I am about to embark on a development where knowing that this feature will remain available would make a big difference. Are you in a position to say this yet?
|
| Philipp Matuschka (MMB) 9/4/2010 8:09:33 AM Surely when you first create this m:m relationship it should create the milti reference field in both tables, not just one of them
|
| Kirill Bondar 9/6/2010 3:08:50 AM @Phillip
1. This feature will be definitely included in TeamDesk. At the moment it is incomplete and published through Labs to let you have an early preview.
2. In this article's example you will add languages to persons, but adding persons to languages? Unlikely. Anyway our goal is to extend options to create multireference columns and to allow attaching it to existing tables of certain structure.
|
| Kirill Bondar 9/6/2010 3:10:38 AM @beo: first class idea )
|
| Philipp Matuschka (MMB) 9/6/2010 3:19:05 AM The example I have is where multiple people have multiple relationships with multiple companies. In this case you definitely need the Multi Ref colimn on both sides of the relattionship.
This is great functionality. I already assumed that you would keep it and did some work based on it. Keep it up.
|
| Kirill Bondar 9/6/2010 7:04:10 AM You can now create multireference columns based on existing link tables.
@Phillip - you can now create multiref for both sides
@Rick - there is no need to import-export data anymore
Dropping multiref column does not drop link table and relationships - even if you dropped it you can recreate it anytime.
|
| Rick Cogley 9/6/2010 7:22:29 AM Kirill, outstanding! What's the procedure to convert?
|
| Kirill Bondar 9/6/2010 7:34:01 AM @Rick - create new multireference, pick up the table from the dropdown. On the next step you'll be presented with choice to create new link table or pick up existing one.
|
| Liquid Rapid 9/6/2010 4:33:37 PM I also agree with beo's idea of multi-select in the record picker. That would be a great way to pick many records for a multireference.
|
| beo 9/7/2010 1:24:29 AM I would like to be able to create different record picker for different view. But that's another idea ...
|
| Rick Cogley 9/20/2010 7:47:29 AM I noticed something really nice. When I specify the name column inside the multi-reference column setup, as something other than the table's name column (we have been setting a Display Name here), this feature gracefully falls back to the table's name column, when the specified column is blank.
Let's say I have Last Name and Last Name Japanese. Last Name is the default display column for the Contacts table. I want the multi-reference column to display Japanese, though. So I specify Last Name Japanese in the multi-ref column setup. In a view, if that is blank, it will fall back to displaying just the Last Name (English).
Can you verify this is what is happening? Or am I mis-understanding.
|
| Rick Cogley 9/20/2010 9:08:06 PM Oops, it was not doing that at all. Sorry. I figured out how we can make that work, with a Display Name that has a formula to grab whatever text fields exist and display it in a particular way.
|
| Rick Cogley 9/20/2010 9:10:33 PM A little feedback -
* the name of the relations are blank, which I am not sure I understand. Did you mean for us to name them whatever? Will that just get overwritten?
* The Add behavior makes you click Add, then open the dropdown and select from the list. If I click Add, though, I would like it to just jump to the next step, open the popup so I can choose. Fewer clicks that way. Also, it is a wishlist type of thing, but it would be great if there were a way to keep selecting contacts I want to add, in the select form that pops up. Then when I close it, they are all added.
|
| Kirill Bondar 9/29/2010 8:40:50 AM @Rick:
1. Whenever we need to display a relationship in setup section (for example, in column's usage section) we either use the name, if provided, or generating description based on tables, reference and key columns, for example: Master[Id]<-Detail[Master Id]
2. So far we are using standard record picker controls with minimal intrusion in their logic. This may be changed or improved in the future.
|
| Kirill Bondar 9/29/2010 8:45:56 AM We have extended the API to handle Multi-Reference columns: they are now properly reported via column description's Type and Kind property. If your application uses Multi-Reference columns and you are utilizing the SOAP API to query the data you may need to update your client part to reflect newer SOAP service definitions. As (Obsolete) TeamDesk Backup tool and (Obsolete) TeamDesk Backup GUI utilize the API to backup the data, you will need to upgrade to newer versions. |
| Rick Cogley 9/29/2010 9:27:10 AM I have no complaints - it really works well and is just what we wanted. Thank you for doing this, Kirill. It is also nice to see the new versions of the backup tool too.
|
| Kirill Bondar 10/6/2010 10:04:07 AM Moving closer to final release:
1. Fixed encoding problem of certain characters (e.g. when comma appeared as \44)
2. Made multiref work with Add Similar
3. Master table's key column no longer limited to Text type
|
| Liquid Rapid 10/6/2010 1:45:56 PM That's great, Kirill. Thanks for the update. I'm looking forward to the release!
About 2. - Does that mean if you Add Similar on a record with a multiref, all the multiref links are copied to the new record?
|
 | Slava Shinderov 10/6/2010 2:13:58 PM @Nathan: Yes, if "Allow add similar" is checked in the multi-ref column.
|
| Liquid Rapid 10/6/2010 5:11:38 PM Thanks, Slava. That's a cool feature - and an additional benefit over manually-created many-to-many links. Nice.
|
 | Gii Systems 10/7/2010 8:54:56 AM I am trying to get a 'Count' of the number of records linked via the Multi Ref. The count only updates once the record is saved. Can we do a live update within the form, before the record is saved?
|
| Kirill Bondar 10/14/2010 8:50:41 AM @Jacques: no, at least not at the moment
|
| Kirill Bondar 10/14/2010 8:50:48 AM |
| Liquid Rapid 10/14/2010 1:51:16 PM Looks great, Kirill. Thanks. Does selecting Many-to-One let you create a multireference column on the Many side of a Many-to-One relationship? Or does it actually create a link table and convert the relationship into a Many-to-Many relationship?
|
| Kirill Bondar 10/14/2010 1:55:14 PM @Nathan: We are planning to hide current one-to-many relationship concept behind a sort of "Single Reference Column" - details will be posted to Idea Exchange as soon as we'll round them up. So far many-to-one simply creates the one-to-many relationship using user's choice as the master table and current table as the detail.
|
| Liquid Rapid 10/14/2010 2:08:44 PM Nice! I'm looking forward to hearing about the new feature. That'll be very useful.
|
| Kirill Bondar 1/26/2011 9:23:26 AM Merged with:
174 - Create Multiple records in a table at one time (with record picker)
|
 | gerardo garcia 11/30/2011 9:45:45 PM A "required" data validation on a multireference column may be required.
I have it working by now using validation rules.
Do I need to promote this implemented idea to receive comment updates or just by submitting a comment I will now be copied on replies?
Thanks!
|
| Slava Shinderov 12/1/2011 6:56:01 AM @Gerardo you may consider using form behavior in order to set multi-reference column as required.
|
| gerardo garcia 12/10/2011 10:20:38 PM Thanks Slava, using it and works good.
As a suggestion would be nice if the already selected registers would not show on the dropdrop down list, and also to have an option of removing all or adding all related records.
Thanks.
|
| Kirill Bondar 8/16/2012 5:50:45 AM We want to make one breaking change for a multi-reference columns.
At the moment **when used in formulas** multi-reference column is evaluated as a comma separated list of selected values rendered as text. To evaluate the value we have to query all selected records but we can not imagine why one would be interested in value-as-text. To avoid misuse, we want to restrict the usage of multi-reference in formulas to just two scenarios for which we'll generate specially optimized code:
In([Key], [MultiReference]) to check whether the key is present in the list of selected records (this is already online) and
IsNull([MultiReference]) to check whether something is selected.
What do you think?
|
| Philipp Matuschka (MMB) 8/16/2012 7:09:00 AM Seems OK to me
|
| gerardo garcia 8/24/2012 11:16:16 PM Hello Kirill
Does In([Key], [MultiReference]) works with the USER PROPERTY TABLE?
I was trying to do : In([User],[Users])
Where [User] would be the user property table key or in other words, the current User, and [Users] a multireference to the Users. But the field [User] wont appear in the formula editor.
Then I tried In(User(),[Users]) and that seems to work.
|
| Kirill Bondar 8/28/2012 8:47:29 AM @Gerardo: at the moment we do not allow to reference the key column of the user property table - I have to check whether it is a bug or feature:) As a side note, User() is faster since we simply substitute it with current user value without accessing the database. In contrast, [User] will require database access to check whether current user's record is present in a user property table (in background query the condition [User]=User() is checked). If all of your users have a record in a userprop, User() is simply faster.
|
