none
using multivalue lookup in a web datase query

    Question

  • Hi,

    My question is related to access2010 web database, I am trying to use a multivalue field in join condition but get message saying "The multi-valued field is not valid in the specified join clause", my query is joining products and requirements, where requirements table has a multivalue lookup to products allowing to select all products a particular requirement is met by.

    I know it is not good to use multivalue lookup fields, but in my case data resides in sharepoint 2010 lists, and access opens them as linked tables, the query works fine in a normal access database but fails in web database, I have a strong feeling that this is not supported in web database, but this is a basic query if this doesn't work then what is point in enabling multivalue lookup fields in web database like many other features this should have been disabled in web database.

    Any suggestions please?

    Regards,

    Santhosh

     


    santhosh
    Monday, May 16, 2011 4:05 PM

Answers

  • I think most here have this covered quite well.

    At the end of the day we simply don't have the ability to join multi value fields in this fashion for web systems.  Those MV fields are still useful when you just need to pick a list of favorite colors for example. And that cool multi select combo "check box": does work in web forms. This is a rather cool interface control and I REALLY wish it was exposed and available for non multi value fields.

    I am talking about this this one:


    However if the other table you're going to join has additional columns beyond a simple ID and say a "color" value as per above, then realistically your choice is to avoid and not use a multi value column.

    As a few others pointed out, you don't have the relationship view in web databases, but you can use the look up and relationships wizard to build relationships. Those relationships have basic features such as cascade delete etc.

    Building standard relationships in a web databases means  you are thus free to join in those additional tables in a web query like we always could in the past. 

    I'm not sure if it's much consolation here, but the real trick in web development is learning along the way what necessarily doesn't work, and hoping we don't get bit too hard when we run into such brick walls.

    Albert D. Kallal  (Access MVP)
    Edmonton, Alberta Canada

    • Proposed as answer by Bruce Song Friday, May 27, 2011 11:22 AM
    • Marked as answer by Bruce Song Tuesday, May 31, 2011 10:11 AM
    Saturday, May 21, 2011 6:18 AM

All replies

  • Hi Santhosh,

    I did some research about your problem. I used the Access 2010 web database sample-Issues database and refernece the article: http://office.microsoft.com/en-us/access-help/store-multiple-values-in-a-lookup-field-HA010341483.aspx, then found it can create the mutiple values look up through right clicking the field name and choose Lookup & Relationship, you can see my screen shot:

    I think it could be the problem with the link table to Sharepoint. Actually, I am not very familiar with Sharepoint related technology.

    If this does not help you, you may try to ask on the sharepoint site:http://social.msdn.microsoft.com/Forums/en-US/category/sharepoint2010, you may get more suggestions from there.

    Best Regards,

     


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, May 18, 2011 5:34 AM
  • Hi Bruce,

    Thankyou for your response, but the issue I have is not in creating a multivalue lookup field but using it in the join condition, in the below web query products in requirements is a multivalue lookup field from products table, when I try the below join it doesn't work.

    Regards,

    Santhosh


    santhosh
    • Edited by SanRek Wednesday, May 18, 2011 9:18 AM
    Wednesday, May 18, 2011 8:37 AM
  • Hi Santhosh,

    could you finally show us your screenshot? I really want to llok at it.

    However, there 2 remarks. First, you can check this article http://office.microsoft.com/en-us/access-help/using-multivalued-fields-in-queries-HA010149297.aspx where the information about joining multi-valued fileds. But since you said you had been able to run your query in a client application, I'm afraid this won't be helpful.

    There is also another one amazing document which is called Access 2010-Improving the Reach and Manageability of your Database Applications.docx. And here is a qoute:

    Also, note that each lookup column a query uses adds an extra data source, because of the hidden join needed to retrieve the displayed value.

    I'm not sure but it sounds like a conflict between hidden join and your the same but 'visible' self-forced join. You can download this doc and try to explore restrictions for Web dbs. There are a lot of useful information.

     


    Andrey V Artemyev | Saint-Petersburg, Russia
    Wednesday, May 18, 2011 9:03 AM
  • Hi Andrey,

    Thankyou for the reply,I have updated my post above with the image, did not realise that image had not gone thru.

    Regards,

    Santhosh


    santhosh
    Wednesday, May 18, 2011 9:19 AM
  • Hi Santhosh,

    How did you get the the Relationship diagram? It seems that your Access file is not a web database Access. I try to join the the table fileds from the diagram, but the Relationships button is disabled. You can see my screen shot:

    So, I think we can't join tables in web database.

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    • Edited by Bruce Song Thursday, May 19, 2011 10:46 AM
    Thursday, May 19, 2011 10:35 AM
  • Bruce,

    I think it's not a DB Relationships window but a Web Query one. I have no A2010 right now, so, you may try to create a Web Query, add 2 tables and join them to reproduce this behaviour.

    As I understand from the link I gave above, such fields should be joined by Field.Value rathen by the whole multi-valued field. But it was described for the client dbs, not sure it is acceptable in Web dbs.


    Andrey V Artemyev | Saint-Petersburg, Russia
    Thursday, May 19, 2011 10:45 AM
  • Hi Bruce,

    I don't think relationships is enabled for web database, relationship needs to be created using lookupcolumns between tables thats the only way in web database. When you create a web query add the required tables in design mode and join the fields.

    Regards,

    Santhosh


    santhosh
    Thursday, May 19, 2011 10:59 AM
  • Hi Andrey,

    Yep you are correct, Field.value has to be used in the join condition, but the table in the webquery does not display the value field.

    Regards,

    Santhosh


    santhosh
    Thursday, May 19, 2011 11:17 AM
  • Santhosh,

    I was able to reproduce this issue. I noticed that the problem is coming from 'Allow multiple values' option. As soon as you allow to choose only one value for a look-up field, you can use it in joining. 

    This issue is very similar to the problem of filtering multi-valued field in the SharePoint list. For example, you may assign one task to more than one employee, and this field immediately becomes disabled for filtering by its values.

    If there is a workaround or some guidelines, I would be very glad to hear this info.


    Andrey V Artemyev | Saint-Petersburg, Russia
    Thursday, May 19, 2011 4:02 PM
  • Hi Santhosh and Andrey,

    Thank you for reminding me the web query. I also reproduced the scenario, take a look at my screen shot:

    I will involve some one who are more familiar with the multi-valued look up feature of Access web database.

    You may need to wait for some time. Appreciate your patience.

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.



    Friday, May 20, 2011 6:59 AM
  • I think most here have this covered quite well.

    At the end of the day we simply don't have the ability to join multi value fields in this fashion for web systems.  Those MV fields are still useful when you just need to pick a list of favorite colors for example. And that cool multi select combo "check box": does work in web forms. This is a rather cool interface control and I REALLY wish it was exposed and available for non multi value fields.

    I am talking about this this one:


    However if the other table you're going to join has additional columns beyond a simple ID and say a "color" value as per above, then realistically your choice is to avoid and not use a multi value column.

    As a few others pointed out, you don't have the relationship view in web databases, but you can use the look up and relationships wizard to build relationships. Those relationships have basic features such as cascade delete etc.

    Building standard relationships in a web databases means  you are thus free to join in those additional tables in a web query like we always could in the past. 

    I'm not sure if it's much consolation here, but the real trick in web development is learning along the way what necessarily doesn't work, and hoping we don't get bit too hard when we run into such brick walls.

    Albert D. Kallal  (Access MVP)
    Edmonton, Alberta Canada

    • Proposed as answer by Bruce Song Friday, May 27, 2011 11:22 AM
    • Marked as answer by Bruce Song Tuesday, May 31, 2011 10:11 AM
    Saturday, May 21, 2011 6:18 AM
  • Hi Albert,

    Thanks for your reply.

    will have to live with this limitation for now and work around this.

    Regards


    santhosh
    Tuesday, May 24, 2011 10:59 AM
  • Albert et al.,

    Thanks for the great input about issues surrounding limitations of using a multivalue lookup field in a web db.  Would you please speak a bit to other ways to create the same type of functionality in a web db that works around the use of lookup fields?  To use Albert's example of a colors 'check box' combo box, I want to have the data entry form like the one in Albert's screenshot where the user can select multiple colors from a list.  I want the list of colors to be stored in a table called tblColors in case I discover at some point that I want to add more colors to the list easily, and so that I can perhaps create another form that users can use to add their favorite colors to the list if they don't already find them there.  But like we see in Albert's example, I want the data entry to place the selected colors into tblContacts in the FavColors field.  I also want the user to be able to pull up their record and edit FavColors if they change their mind about their selections.

    Most importantly to this discussion, I want to be able to do some statistical analysis.  I need to be able to query [FavColors] where [FavColors] = Like '*blue*'.  So I guess the questions is, how would you go about creating a structure that creates a multi-selection list box (as I understand it, you can't create a multi-selection combo box without using a lookup field) in a form that can be used for data entry or data editing, and store the data in the tblContacts.FavColors field in the format Color1, Color2, Color3 so that it can be queried using the Like operator?

    Thanks,

    Craig

    Wednesday, June 15, 2011 5:11 PM
  • Great question....

    The way you approach this, is to use a classical or longtime traditional approach that works well in Access, and in fact for most database products.

    The traditional and classical way to do this, is to build a sub form. This is not quite as slick as the little combo check box control that I have in the above web screen shot, but it still quite workable.

    In fact as I pointed out, for some of my web applications I would actually use the multi value fields since the above control combo picker is oh so very cool. However, this combo picker is only a realistic good choice if using such a control and multi value fields would not cause me to be restricted in doing reports or other things later on in the application (such as the wall or restrictions you've into here with your question + example here).

    For example, in my room booking application, I want to list out and allow the user to select what equipment is going to be required for the room booking. The web form this looks like this:

    So the above the equipment list is a standard relationship. I used a sub form that has a continues form, and allows one to add to the "tblListOfRequiredEquipment". And of course I use a combo box in that continues form that is feed from tblEquipmentTable.

    So, be it a list of favorate foods, colors, or above equipment as above, the long time suggested approach of a sub form works quite well.  So, the above quite much how we did this in the past, and it translates quite well for web based apps also.

    Albert D. Kallal  (Access MVP)
    Edmonton, Alberta Canada

     

    Thursday, June 16, 2011 4:10 PM
  • Fantastic response.  Thanks for that.  Sometimes I get so wrapped up in trying to figure out how to use the new functions that I forget about the basics! :-)
    Friday, June 17, 2011 5:01 PM