Multivalue Fields Pros and Cons

[Zydeceltico]

Hi All -

I am trying to make a decision before making a relatively major commitment of time and headache.

I am intrigued about *the Idea* of using a multivalued field as described here:

https://support.office.com/en-us/article/create-or-delete-a-multivalued-field-7c2fd644-3771-48e4-b6dc-6de9bebbec31?ui=en-US&rs=en-US&ad=US

There are two conditions in the real world that interest me.

1) My db is all about manufacturing inspections. There are inherently A LOT of inspection items. Often these items occur as groups of conditions. It would be great to group some inspection items together BUT I would want to tease them apart later.

2) Any given production line will typically have between 2 and 8 employees working on it. However, the same "team" of employees is not assigned to the same production line every day. There are no permanently defined teams. Sometimes the production line has to stop. Sometimes for hours. We want to track who all was on the production line while it was stopped and for how long.

At first glance, multivalued fields seem like they might be helpful for me - but I have learned that what I think I am reading and what is reality are often two different things.

SO I am looking for feedback on using multivalued fields. I am particularly interested in what is entailed in:
1) if the Rowsource can be a query - and what does that look like;
2) What operations can be performed on a given multivalued record? (i.e., what do query results of multivalued fields look like and what can be done with them;
3) Re: the "team" of employees condition mentioned above - and given that I would like to populate the mutlivalued field with a query of tblPersonnel, and given that each individual has a unique ID autonumber in tblPersonnel: what happens to the reference to the individual employee's related unique ID autonumber when multiple employees' names are recorded in a multivalued field? Can they be parsed apart with minimal effort? Or does it become impossible to relate the names within a field with the original individual IDs?

Mostly appreciative of insight from all of you before I "jump in the deep water."

Thanks,

Tim

 

[isladogs]

Most experienced developers will strongly advise not to use MVFs.
I've written an extended article explaining many of the reasons why they are a bad idea.
http://www.mendipdatasystems.co.uk/multivalued-fields/4594468763

 

[MajP]

If you completely understand how these work then I would consider it with caution. I understand them and how they normalize the data behind the scenes, and I am very cautious to ever use them. But I would only use this to store values and not keys. If you want to assign colors "Red", "Blue" "Green" that would be ok. If you want to store employeeID 1 and show John Smith, it gets so convoluted I can barely wrap my head around it.

You can query Green and any records with Green as one of the values will be returned. Basically the values are stored in a related child table just like you would do if you did this in a traditional manner.

 

[MajP]

Also I think you mentioned this may get moved to SQL backend, so then you would have to redesign in the future.

 

[Zydeceltico]

If you want to store employeeID 1 and show John Smith, it gets so convoluted I can barely wrap my head around it.

Thanks. That what I figured - - - and that's all I needed to hear! Moving on.

 

[Zydeceltico]

Most experienced developers will strongly advise not to use MVFs.
I've written an extended article explaining many of the reasons why they are a bad idea.
http://www.mendipdatasystems.co.uk/multivalued-fields/4594468763

Wow - steer clear. Looks like a nightmare.

Any ideas on alternatives that approximate the idea in order to build my "teams" of employees? I'm guessing either a junction table or a "grouping" table like I use to group parts that make up final products (i.e., tblParts -> tblAssemblyComponents -> tblFinalProducts).

 

[MajP]

Thanks. That what I figured - - - and that's all I needed to hear! Moving on.

So to understand just how convoluted this is, imagine you have a mvf field that holds selected employees for your inspection event. For InspectionEvent A with PK of 123

In the field you see
John Smith; Mike Brown; Sue Black

Well none of that actually exists in that field, it is basically a calculated field. In some related, not visible, and created behind the scenes table you are actually storing the normalized values
empID InspectionEvent_FK
1 123 'john
33 123 'Mike
22 123 'Sue

And the display which acts like a combo is displaying the employee names from the employee table in a concatenated fashion, but using the linked names from the employee table.
That is some serious Elfin magic, that will make you head hurt if trying to do any type of recordset manipulation.

 

[Mark_]

(i.e., tblParts -> tblAssemblyComponents -> tblFinalProducts).

When dealing with assembled "Parts", say a 10mm nut and 10mm bolt (length 25mm), what you have is ONE table that holds "Item" and a "Group" table that links between records in your "Item" table.

You would then see data that looks like;
TblItem
ID-Part
1-Nut 10mm
2-Bolt 10mm
3-Nut&Bolt 10mm

TblGroup
ItemID-GroupID (Both point to TblItem ID, one is the item you are attaching, the other is the item attached to)
1-3
2-3

This way you can link multiple items to make assemblies. This avoids you trying to work out how many levels of "Sub-assembly/assembly/sub-product/product/product bundle/what have you"

Often you will add a flag that indicates one product is composed of other products and another for "Sold as a product".

This lets you get tons of material in, assemble for packaging or manufacture, and track what is/isn't being sold as items.

This also means that if you have items that are being tracked you can either save that type of information in your "Item" table or have a child record that is filled in (or linked to) when creating the serialized product.

 

[AccessBlaster]

The multivalued field is a nightmare for the reasons described above. But in the real world you are faced with decisions like the example below.

To optimize the label space and cut down of human error mangers will often create a multivalued field for industry barcoding. This can be done, but it adds to a already complex scheme.

 

[MajP]

To optimize the label space and cut down of human error mangers will often create a multivalued field for industry barcoding. This can be done, but it adds to a already complex scheme

I do not think that is a multi value field as we are talking. That is a single field that represents multiple different pieces of data, not a field containing multiple child records. That is a whole other other issue which has its own potential problems.

 

[Zydeceltico]

When dealing with assembled "Parts", say a 10mm nut and 10mm bolt (length 25mm), what you have is ONE table that holds "Item" and a "Group" table that links between records in your "Item" table.

You would then see data that looks like;
TblItem
ID-Part
1-Nut 10mm
2-Bolt 10mm
3-Nut&Bolt 10mm

TblGroup
ItemID-GroupID (Both point to TblItem ID, one is the item you are attaching, the other is the item attached to)
1-3
2-3

This way you can link multiple items to make assemblies. This avoids you trying to work out how many levels of "Sub-assembly/assembly/sub-product/product/product bundle/what have you"

Often you will add a flag that indicates one product is composed of other products and another for "Sold as a product".

This is exactly how I have my tables setup but I use a 3rd table instead of a flag to indicate the difference between "Final Product" and "Parts."

The final product table includes both assemblies AND all of the component parts. The intermediary table is the assembly table where part IDs are grouped on FinalProductIDs.

My logic with including all of the parts in the Final Product table is that - in fact - if we are selling single parts as final product then they aren't parts anymore and we do indeed treat them differently in business practice.

 

[isladogs]

Personally I would store that barcode sequence info in four fields and concatenate them as needed.
I'm struggling to see how an MVF would be seen to be useful in that example.
If the aim was primarily to save space then surely it would just be saved as a single text field.
Am I missing something here?

 

[Zydeceltico]

I do not think that is a multi value field as we are talking. That is a single field that represents multiple different pieces of data, not a field containing multiple child records. That is a whole other other issue which has its own potential problems.

That's what I got into with our Job Numbers.

For example a typical job number: A632902012 is actually 3 pieces of information "A6329", "020", and "12" all pertain to different things but collectively point to one event. I have three columns in my job table and break it down. Especially because we routinely have:

A632902001
A632902024
A632902003

AND

A632903050
A632903014
etc.

A6329 is the overarching Job number but the other two "###" and "##" represent various other things and need to be tracked individually as well as being able to report on the entire Job Number itself (A6329).

EDIT: I should add that this design is working very very well - at least for my needs.

EDIT2: And I agree - this is not something I would use a multivalued field for myself.

 

[Zydeceltico]

empID InspectionEvent_FK
1 123 'john
33 123 'Mike
22 123 'Sue

What you described sounds extremely convoluted.

OTH, the table layout you described is exactly how the three tables for my Parts, Assemblies, and FinalProducts are laid out.

It works great (3 tables - one being a grouping table) and is easy to work with for - and I can't underscore this enough for anyone else asking the same question that I originally posted - my very specific and very limited needs - primarily because the data in it are extremely static and really typically ever only used for reference. Never calculated on.

As it is - I find myself putting FinalProductID in a public variable routinely so I can pass a query reference to controls on forms for the user to make choices depending on what Part is selected. A whole bunch of info travels with FinalProductID. Same with our Job Numbers which are aggregates of three separate pieces of numeric info concatenated into one 9 digit alpha numeric.

 

[AccessBlaster]

Personally I would store that barcode sequence info in four fields and concatenate them as needed.

Yes of course, except when you are doing inventory you scan that single barcode into a single field. Now you have multiple pieces of data in a single field, and yes you can un-concatenate this data but it's the opposite of good practices. The real world sometimes collides with normalization.

 

[isladogs]

The barcode example is a 'multi-part' field for want of a name.
Whether it is saved as four fields or one, it still isn't an example of a multivalue field as discussed in this thread.
A MVF has several related but separate items in the same field e.g size, colour such as a T-shirt available in sizes S,M,L,XL and colours red,Blue,yellow. Using MVFs mean you could use one record rather than 4*3=12 records for that data.
However almost always a bad idea.

 

[AccessBlaster]

Fair enough Colin.

 

[gemma-the-husky]

fwiw, In a recent app, I wanted to store the selections from a list, and come back to them later.

The only way I could see to do this, was this

Instead of binding the form control to a field, I left the control unbound, but populated the control by generating a value list for the row source. ((in this case, 2 columns, index and decsription)

So as I selected items in the list box, I iterated the items and stored the ID's of the selected items in a not visible bound string field with a separator character. Then when I reopened the form, I could reselect the select items by processing the string field.

Where this failed was if I needed to change the records in the value list, as the previously selected items didn't automatically respond correctly to changing the record source.

All this was in A2003, which didn't have MVFs. Anyway, I think I can get there with a little more thought, and I imagine that a MVF is doing something like this.

There are a couple of technical issues. If you delete a row from the souce table, where the row has already been selected in a MVF control, how does the MVF record keeping tidy itself up. And as I noted, how does the display manage itself if you add more items to the list.

[edit. I fixed these issues now. I clear all the list selections, requery the list itself, and then re-set the selections]

Anyway, I hope these thoughts give you some ideas into what might be going on.