Unable to get the lookup field value through the formula written in Calculated value control - InfoPath Dev
in

InfoPath Dev

Use our Google Custom Search for best site search results.

Unable to get the lookup field value through the formula written in Calculated value control

Last post 07-23-2012 12:16 AM by Gennady Vanin (Геннадий Ванин, Новосибирск). 5 replies.
Page 1 of 1 (6 items)
Sort Posts: Previous Next
  • 07-18-2012 05:18 AM

    Unable to get the lookup field value through the formula written in Calculated value control

     Hi,

      I have 3 lists. Where in 1st(ClientList) list ClientID is the Unique, in 2nd list(RequirementList
    ) ReqmtID the Unique and in 3rd list(DeliveryList) CandID is the unique value.

    ClientList          
    ClientName(single line text)        
    ClientID(single line text)            
        
    RequirementList
    ReqmtID(single line text)
    ReqDesc(single line text)
    ClientID("looks up" the ClientID column of ClientList)
    ClientID: ClientName

    DeliveryList
    CandID(single line text)
    ReqmtID("looks up" the ReqmtID column of RequirementList)

    Now I want to get the "ClientName"(based on the ReqmtID) automatically when I select ReqmtID(from the drop down) in the DeliveryList While adding the new item.
    I Added 2 list data connections, one receive data from ClientList and the other receive data from RequirementList.
    then Added a calculated value control to the form and add following formula to the calculated value control:

    xdXDocument:GetDOM("ClientList")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:ClientName[../d:ClientID = xdXDocument:GetDOM("RequirementList1")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:ClientID[../d:ReqmtID = xdXDocument:get-DOM()/dfs:myFields/dfs:dataFields/my:SharePointListItem_RW/my:ReqmtID]]
    But no result.
    Then I tested with some formulas:
    1. If I select only ReqmtID from RequirementList1 filtered by RequirementList1:ReqmtID = DeliveryList:ReqmtID         ans: value of ReqmtID [but it throws error at the dropdown as only positive integers are allowed in Preview]
    2.Then ReqDesc from RequirementList1 filtered by RequirementList1:ReqmtID = DeliveryList:ReqmtID          ans: value of ReqDesc(without any error)
    3. Then to get the RequirementList1 ClientID from  RequirementList1:ReqmtID = DeliveryList:ReqmtID

    Now I am getting the answer as 1,2... for ClientID instead of the value it consists.

    Means It gives wrong answer only with the lookup column(May be it is giving Id value instead of the actual value it consists.)  Please anyone help me to get more knowledge on this and solve this issue.

    Thanks in advance

  • 07-18-2012 11:22 AM In reply to

    Re: Unable to get the lookup field value through the formula written in Calculated value control

    Is the ClientID field also in your main data source?

    Hilary Stoupa

  • 07-18-2012 11:21 PM In reply to

    Re: Unable to get the lookup field value through the formula written in Calculated value control

    No its not in Main DataSource. I want to get it from the 1st(ClientList) list through 2nd list(RequirementList.
  • 07-19-2012 07:35 AM In reply to

    Re: Unable to get the lookup field value through the formula written in Calculated value control

    Mind attaching your template under the Options tab in a reply? That would give me a better idea of what you are trying to do.

    Hilary Stoupa

  • 07-19-2012 07:58 PM In reply to

    Re: Unable to get the lookup field value through the formula written in Calculated value control

    VidyaPai:

      I have 3 lists. Where in 1st(ClientList) list ClientID is the Unique, in 2nd list(RequirementList
    ) ReqmtID the Unique and in 3rd list(DeliveryList) CandID is the unique value.

    ClientList          

    • ClientName(single line text)        
    • ClientID(single line text)            

        
    RequirementList

    • ReqmtID(single line text)
    • ReqDesc(single line text)
    • ClientID("looks up" the ClientID column of ClientList)
    • ClientID: ClientName


    DeliveryList

    • CandID(single line text)
    • ReqmtID("looks up" the ReqmtID column of RequirementList)


    Now I want to get the "ClientName"(based on the ReqmtID) automatically when I select ReqmtID(from the drop down) in the DeliveryList While adding the new item

    You want "ClientName" (based on the ReqmtID)" but from which field of which list (you have it in 2 lists) to where?
    and on which actions? and to where - the fields of Infopath form or fields of lists and which?

     

    VidyaPai:
    Now I want to get the "ClientName"(based on the ReqmtID) automatically when I select ReqmtID(from the drop down) in the DeliveryList While adding the new item

    But in your description the ReqmtID depends on ClientName but. but not vice versa, ClientName is independent on ReqmtID, i.e. it is possible to uniquely determine ClientName based on ReqmtID value 

     "While adding the new item" to what? by which actions? where?

    Where is calculated field and what's for?

    VidyaPai:
    I Added 2 list data connections, one receive data from ClientList and the other receive data from RequirementList.
    then Added a calculated value control to the form and add following formula to the calculated value control:

    xdXDocument:GetDOM("ClientList")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:ClientName[../d:ClientID = xdXDocument:GetDOM("RequirementList1")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:ClientID[../d:ReqmtID = xdXDocument:get-DOM()/dfs:myFields/dfs:dataFields/my:SharePointListItem_RW/my:ReqmtID]]
    But no result

    It is incomprehensible - you are describing how you are solving something which you never formulated.

    Please describe what you want but NOT HOW (you failed to solve something you never described)

     

    Gennady Vanin (Novosibirsk) --- Геннадий Ванин (Новосибирск)
  • 07-23-2012 12:16 AM In reply to

    Re: Unable to get the lookup field value through the formula written in Calculated value control

    Enclosed please find the template
    • Unable to get the lookup field value through.xsn

    that shows and updates Infopath form of DeliveryList list

    I've cleaned unnecessary fields. having created ists:

     

    ClientList with fields::

    • ClientName (Single Line of text)
      Title_ClientList (renamed the default Title)

    RequirementList with fields:

    • ClientListID_Lookup (Lookup for ID of ClientList)
      ReqDesc (Single Line of text)
      TitleRequirementList (renamed from Title)

     

    DeliveryList with fields:

    • ReqmtID (Lookup for ID in ReqmtList)
      CandID (Single Line of text, where I render ID of chosen client from ClientList for chosen ReqmtID)
      CandName (Single Line of text, where I show ClientName of chosen client from ClientList for chosen ReqmtID)
      Title_DeliveryList (renamed Title)

    and filled with some data

     

     

     

     

    Navigated to DeliveryList and pressed "Modify Form" button which opened it for editing in Infopath Designer 2010

    Edited and published there the form

     

     Having clicked on ribbon "From Sharepoint List" created the data conncections for 3 lists:

    • ClientList_Wizard
    • RequirementList_Wizard
    • DeliveryList_Wizard
    for corresponding 3 lists. 

    On ribbon clicked Data > Show Fields > Show Advanced View (to switch from Basic View to Asvanced View)

     Having chosen ReqmtID drop-down ListBox created 2 rules "Set a field's value":

    • Set CandID
      to fill CandID  with IDs of ClientList items corresponding to chosen ReqmtID 
    • Set CandName with ClientName corresponding to chosen ReqmtID
       

     

     The first rule "Value:" expression:

    • ClientListIDLookup[ID = ReqmtID]

    I simply copy-pasted having in mind it as already "calculated" ClientList item ID for corresponding client name,

    getting for a 2nd rule:

    •  ClientName[ID = ClientListIDLookup[ID = ReqmtID]]

     

     

     

     

    Pressed "Quick Publish" )under File tab)  and clicked the link in Publish message box: :

    • Open the Sharepoint list in browser
      https://gennadyvanin.sharepoint.com/Lists/DeliveryList/AllItems.aspx 

     

     

     

     

     

     

     

     

     

     

     

     

     Note that I have used only codeless (without any coding) clicking and copy-pasting through Infopath GUI

     

    Gennady Vanin (Novosibirsk) --- Геннадий Ванин (Новосибирск)
Page 1 of 1 (6 items)
Copyright © 2003-2019 Qdabra Software. All rights reserved.
View our Terms of Use.