none
Copy lookup field value in another column - Access Web App RRS feed

  • Question

  • Hi All,

    I have table in my access app that is coming from SharePoint list. The SharePoint list has a number field called ProjectId which is used as lookup in multiple tables in my access app. However not all queries are working on this lookup field. However if I use a number field instead of lookup my query it works fine. Hence I decided to copy the Lookup value in number field as soon as record is inserted.

    However, the the value copied is not correct. Below is my implementation. Please help.

    I want to copy ProjectId in ProjectNumber field as soon as record is created. I have added the below query on Insert.

    However, the data that is copied in the ProjectNumber field is different. It is copying the ID field from the lookup table list. Below is the result and snap shot of master table:

    Any help appreciated. Thanks in advance.

    Regards,

    Monali


    Monali



    • Edited by MonaliB Tuesday, November 24, 2015 1:49 PM More info added
    Tuesday, November 24, 2015 1:29 PM

All replies

  • Hi Monali,

    Your issue was caused by that value of lookup field is index instead of the real value.

    To achieve your requirement, you could find the real value in the table which you used for lookup field, and then set the value when you needed.

    The image below shows a simple demo. The UserId is a lookup field in UImage, and it is binding to tblUser.id.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, November 25, 2015 2:52 AM
  • Hi Edward,

    Thank you for your reply. The real value is coming from a table that is created by using datasource as SharePoint list hence I do the Table name when I lookup a record. I tried to create a query on the table, but the query is also not populating in the "Look Up a Record In" statement.

    Any other alternative?

    Thanks & Regards,

    Monali


    Monali

    Wednesday, November 25, 2015 9:32 AM
  • Hi MonaliB,

    >> I tried to create a query on the table, but the query is also not populating in the "Look Up a Record In" statement.

    Where did you create this query? If you create query in Access web app, it should appear in “Look Up a Record in” statement. If you enter the query name manually, and launch your web app, would you get error message?

    >> The real value is coming from a table that is created by using datasource as SharePoint list hence I do the Table name when I lookup a record

    Where the table in the last image in your original post? Why did you create a new query instead of using this table? You could use this table in “Look Up a Record in” statement.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, November 26, 2015 3:12 AM