Qdabra qRules

Re: Fill in repeating table based on multiple selections

Hilary Stoupa — Tue, 21 Feb 2012 23:33:00 GMT

For the user info list, I'd try:

https://sharepoint.mycompany.com/_vti_bin/listdata.svc

Re: Fill in repeating table based on multiple selections

bsieloff — Tue, 21 Feb 2012 22:56:10 GMT

For my DataConnection I am using the following connection string:

https://sharepoint.mycompany.com/departments/legal/contractmgmt/_vti_bin/listdata.svc/Contracts()?$select=CompanyName,ContractDateNeeded,Id,CompanyContact,StartDate,CompanyAddress,CompanyCity,CompanyState,CompanyZip,CompanyContactPhone,CompanyContactEmail,ContractAmount,ContractType,BusinessUnit,ContractNotes

this statement seems to work fine, as I can view my results in the browser and in the form if I do a filter on the Change REST URL and execute the query again.

Once I put in the ID number in (), I do receive a reply from the browser, and I can see the data by viewing the source but, agreed, I do not get a result when executing through IP

https://sharepoint.mycompany.com/departments/legal/contractmgmt/_vti_bin/listdata.svc/Contracts(366)

other example above in thread...

For now, I abandoned using the id in the () and am filtering on another unique identifier, although I think it would be smarter to be able to use the ID within the brackets for selecting the data.

I have reviewed your video a few times and it looks very similar, just different results.

One question I did have was how did you get the data from the user information list? I tried https://sharepoint.mycompany.com/_catalogs/users/_vit_bin/listdata.svc but that did not work. This data would be very handy.

Thank you,

Re: Fill in repeating table based on multiple selections

Hilary Stoupa — Tue, 21 Feb 2012 22:05:31 GMT

Okay - so at risk of stating the obvious, the answer to your question of why you don't have a value when you use the data source for a value is because.... there is no data in the adapter.

The question then, is why.

What was the URL you used to create your data connection in IP?

What is the URL you are currently using? You have a couple samples in your post above this.

Also - crazy question - you are running the query again after changing the URL, correct? (This is a step I've been known to forget).

Re: Fill in repeating table based on multiple selections

bsieloff — Tue, 21 Feb 2012 21:08:46 GMT

I had tried that and received nothing. I just tried again by dragging over the <<odata connection>> m.properties folder onto the form as a repeating section, but when I execute the Change Rest URL, it does nothing, I do not even see the field boxes that I see in design mode.

I have noticed a difference when querying in the browser, I don't see a list of the .xml documents, as I have in the past not querying for a single record (".../Contracts(378)" ), but if I view the source I do see the one record and the data specified in the select statement.

I thought this would be the easy part, but I'm slowly learning there is no such part, ever. -sheeesh

Thanks for your help

Re: Fill in repeating table based on multiple selections

Hilary Stoupa — Tue, 21 Feb 2012 20:37:55 GMT

Drag the data fields from your data connection onto your form canvas, and preview. Perform whatever action you need to in the form to execute the query. Do you then see data in the secondary data connection in your form?

Re: Fill in repeating table based on multiple selections

bsieloff — Tue, 21 Feb 2012 20:32:56 GMT

Greetings,

I thought I would post my final query as it may help someone else doing something similar in the future: <<pasted below>>

One more question.

I am now looking to fill in some fields based on the form library row Id. I use listdata.svc in this manner:

concat("https://sharepoint.mycompany.com/departments/legal/contractmgmt/_vti_bin/listdata.svc/Contracts(", SelectedRowID, ")?$select=CompanyName,Id,CompanyContact,StartDate,CompanyAddress,CompanyCity,CompanyState,CompanyZip,CompanyContactPhone,CompanyContactEmail,ContractAmount,ContractType,BusinessUnit,ContractNotes")

to make sure it was working ok, i created a text box and placed the odata query in it to show me the values being passed. A sample looks like:

https://sharepoint.mycompay.com/departments/legal/contractmgmt/_vti_bin/listdata.svc/Contracts(378)?$select=CompanyName,Id,CompanyContact,StartDate,CompanyAddress,CompanyCity,CompanyState,CompanyZip,CompanyContactPhone,CompanyContactEmail,ContractAmount,ContractType,BusinessUnit,ContractNotes

If I copy and past the above link into a browser it does return a result for that selected Id (378, in this example)

<<>>= data connection

I then have a text box named <<main>>"CompanyName" where I set it's value to <<odata connection>>"CompanyName" but nothing/null is shown in the <<main>>"CompanyName" text box

I should only be returned the data for the row id that is selected, which I confirmed, but for some reason when I set a text box value = to the corrisponding odata value it does not show, any ideas why?

Thanks,

-------------- follow up ----------

Data connections with several 'or' statements

concat("https://sharepoint.mycompany.com/departments/legal/contractmgmt/_vti_bin/listdata.svc/Contracts?$select=Title,CompanyName,Id,CompanyContact,StartDate&$filter=((Approver1UserID eq '", FormUserID, "' or Approver2UserID eq '", FormUserID, "' or Approver3UserID eq '", FormUserID, "' or Approver4UserID eq '", FormUserID, "' or Approver5UserID eq '", FormUserID, "' or Approver6UserID eq '", FormUserID, "' or Approver7UserID eq '", FormUserID, "' or Approver8UserID eq '", FormUserID, "') and ApprovalPending ne 'Completed')")

Re: Fill in repeating table based on multiple selections

Hilary Stoupa — Wed, 15 Feb 2012 22:38:33 GMT

Since this is a REST data connection, you'll be able to use rule actions to change the URL and then re-query.

Hopefully it will be faster for you in the browser - you may want to test sooner rather than later... ;-}

Re: Fill in repeating table based on multiple selections

bsieloff — Wed, 15 Feb 2012 22:27:06 GMT

That did it.

Thank you!.. now on to my origianal test of using boolean "or" to check all the Approver fields for a single name.

Seemed very slow in the desinger/preview mode, but hopefully it is much quicker in the browser.

Re: Fill in repeating table based on multiple selections

Hilary Stoupa — Wed, 15 Feb 2012 22:12:38 GMT

I'm a complete dork - your syntax is off - missing a ? before $filter

Re: Fill in repeating table based on multiple selections

Hilary Stoupa — Wed, 15 Feb 2012 22:08:33 GMT

I'm assuming you are using actual values in the Domain\username that have a match, correct?

Re: Fill in repeating table based on multiple selections

bsieloff — Wed, 15 Feb 2012 21:59:31 GMT

ok, good to confirm I was creating the correct syntax before

when I look at the source on the webpage after adding the $filter=Approver1UserID eq 'domain\username' to the UNI, it just states the standard 404 error information

when I create the data connection with the filter in infopath I receive:

1) I'm asked for my credentials ( I type them in, asked for credintials again, type them in, asked for credintials again, ... until I hit cancel)

2) First message from Infopath: The internet address https://sharepoint.mycompany.com/depatments/dept1/contractmgmt/_vti_bi/listdata.svc/Contracts$filter=Approver1UserID eq 'Mydomain\userID' is not valid

3) Second message from infopath: InfoPath cannot open the xml document https://sharepoint.mycompany.com/depatments/dept1/contractmgmt/_vti_bi/listdata.svc/Contracts$filter=Approver1UserID eq 'Mydomain\userID' . The file cannot be accessed

I'm the collection admin

Thanks, -B

Re: Fill in repeating table based on multiple selections

Hilary Stoupa — Wed, 15 Feb 2012 20:51:43 GMT

So, given this:

<d:Approver1UserID>CompanyDomain\mmouse</d:Approver1UserID>

A URL like this should work:

https://sharepoint.mycompany.com/departments/dept1/contractmgmt/_vti_bin/listdata.svc/Contracts$filter=Approver1UserID eq 'CompanyDomain\mmouse'

If your browser claims it can't display anything, be sure to view source on the page....

Does InfoPath give an error if you try to create a data connection with the link above?

Re: Fill in repeating table based on multiple selections

bsieloff — Wed, 15 Feb 2012 20:30:36 GMT

Great links and video... I still think I'm doing this correct, but the computer disagrees,

If I link to contracts, I get a whole lot of this (listed below)

I have been working under the assumption that my field name is the object name, although that may not be the case.

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<feed xml:base="https://fc.CompanyDomainair.com/departments/legal/contractmgmt/_vti_bin/listdata.svc/" xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns="http://www.w3.org/2005/Atom">
<title type="text">Contracts</title>
<id>https://SharePoint.Company.com/departments/legal/contractmgmt/_vti_bin/listdata.svc/Contracts/</id>
<updated>2012-02-15T16:42:23Z</updated>
<link rel="self" title="Contracts" href="Contracts" />
<entry m:etag="W/"13"">
    <id>https://SharePoint.Company.com/departments/legal/contractmgmt/_vti_bin/listdata.svc/Contracts(93)</id>
    <title type="text">Contract.xml</title>
    <updated>2011-08-11T20:47:27-05:00</updated>
    <author>
      <name />
    </author>
    <link m:etag=""{57FAAA8A-98AA-4523-Aa58-129BaaaaaCC8},13"" rel="edit-media" title="ContractsItem" href="Contracts(93)/$value" />
    <link rel="edit" title="ContractsItem" href="Contracts(93)" />
    <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/CreatedBy" type="application/atom+xml;type=entry" title="CreatedBy" href="Contracts(93)/CreatedBy" />
    <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/ModifiedBy" type="application/atom+xml;type=entry" title="ModifiedBy" href="Contracts(93)/ModifiedBy" />
    <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/CheckedOutTo" type="application/atom+xml;type=entry" title="CheckedOutTo" href="Contracts(93)/CheckedOutTo" />
    <category term="Microsoft.SharePoint.DataService.ContractsItem" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme" />
    <content type="text/xml" src="https://fc.CompanyDomainair.com/departments/legal/contractmgmt/Contracts/Contract.xml" />
    <m:properties xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices">
      <d:ContentTypeID>0x01010100E89757AA0F42CD4087AD0B03D010ED87</d:ContentTypeID>
      <d:Name>ContractName.xml</d:Name>
      <d:Title>ContractName.xml</d:Title>
      <d:ManagerName>MickeyMouse</d:ManagerName>
      <d:ManagerEmail>MickeyMouse@Company.com</d:ManagerEmail>
      <d:ManagerAccountID>CompanyDomain\MiniMouse</d:ManagerAccountID>
      <d:CompanyAddress>123 Main Street</d:CompanyAddress>
      <d:CompanyName>Company A</d:CompanyName>
      <d:CompanyCity>AnyWhere</d:CompanyCity>
      <d:CompanyState>AA</d:CompanyState>
      <d:CompanyZip>11122</d:CompanyZip>
      <d:CompanyContact>Michael Jorden</d:CompanyContact>
      <d:CompanyContactPhone>(555) 411-9999</d:CompanyContactPhone>
      <d:ContractType>Vendor Contracts and Agreements</d:ContractType>
      <d:ContractAction>Independent Contractor/Consultant/Subcontractor Agreements (includes temporary employees)</d:ContractAction>
      <d:ContractSubmitted m:type="Edm.Int32" m:null="true"></d:ContractSubmitted>
      <d:Approver1UserID>CompanyDomain\mmouse</d:Approver1UserID>
      <d:Approver2UserID>CompanyDomain\dduck</d:Approver2UserID>
      <d:Approver3UserID>CompanyDomain\minmouse</d:Approver3UserID>
      <d:Approver4UserID>CompanyDomain\hueyduck</d:Approver4UserID>
      <d:Approver5UserID>CompanyDomain\louieduck</d:Approver5UserID>
      <d:Approver6UserID m:null="true"></d:Approver6UserID>
      <d:Approver7UserID m:null="true"></d:Approver7UserID>
      <d:Approver8UserID m:null="true"></d:Approver8UserID>
      <d:Approver1DisplayName>Mickey Mouse</d:Approver1DisplayName>
      <d:Approver2DisplayName>Donald Duck</d:Approver2DisplayName>
      <d:Approver3DisplayName>Mini Mouse</d:Approver3DisplayName>
      <d:Approver4DisplayName>Huey Ducks</d:Approver4DisplayName>
      <d:Approver5DisplayName>Louie Duck</d:Approver5DisplayName>
      <d:Approver6DisplayName m:null="true"></d:Approver6DisplayName>
      <d:Approver7DisplayName m:null="true"></d:Approver7DisplayName>
      <d:Approver8DisplayName m:null="true"></d:Approver8DisplayName>
      <d:CompanyContactEmail>mjordan@bulls.com</d:CompanyContactEmail>
      <d:ContractAmount m:type="Edm.Double">44800</d:ContractAmount>
      <d:ContractDateNeeded m:type="Edm.DateTime">2011-08-01T00:00:00</d:ContractDateNeeded>
      <d:LegalFormation m:null="true"></d:LegalFormation>
      <d:ApprovalPending>Completed</d:ApprovalPending>
      <d:ApprovalPlace>9</d:ApprovalPlace>
      <d:LegalPlace>Completed</d:LegalPlace>
      <d:LegalTime m:type="Edm.DateTime">2011-08-03T16:59:01</d:LegalTime>
      <d:Approval1Time m:type="Edm.DateTime">2011-08-03T17:20:59</d:Approval1Time>
      <d:Approval2Time m:type="Edm.DateTime">2011-08-04T14:43:37</d:Approval2Time>
      <d:Approval3Time m:type="Edm.DateTime">2011-08-04T12:53:47</d:Approval3Time>
      <d:Approval4Time m:type="Edm.DateTime">2011-08-11T20:47:18</d:Approval4Time>
      <d:Approval5Time m:type="Edm.DateTime">2011-08-04T10:21:54</d:Approval5Time>
      <d:Approval6Time m:type="Edm.DateTime" m:null="true"></d:Approval6Time>
      <d:Approval7Time m:type="Edm.DateTime" m:null="true"></d:Approval7Time>
      <d:Approval8Time m:type="Edm.DateTime" m:null="true"></d:Approval8Time>
      <d:CompanyDomainContactUserID>CompanyDomain\pblisard</d:CompanyDomainContactUserID>
      <d:CompanyDomainContactDisplayName>Jason Cohen</d:CompanyDomainContactDisplayName>
      <d:StrFilename>IdeaNova92</d:StrFilename>
      <d:DateSort>Aug 2011</d:DateSort>
      <d:SortYear>2011</d:SortYear>
      <d:SortMonth>Aug</d:SortMonth>
      <d:TerminationReason m:null="true"></d:TerminationReason>
      <d:TermNoticeDays>1</d:TermNoticeDays>
      <d:YNTermNotice>no</d:YNTermNotice>
      <d:YNConvclause>yes</d:YNConvclause>
      <d:YNAssignProvision>yes</d:YNAssignProvision>
      <d:Lockform>yes</d:Lockform>
      <d:FileAttachment m:type="Edm.Int32" m:null="true"></d:FileAttachment>
      <d:ClientContract>Contract.xml</d:ClientContract>
      <d:ParallelSignOff m:type="Edm.Int32" m:null="true"></d:ParallelSignOff>
      <d:ClientFile>Contract.xml</d:ClientFile>
      <d:ContractSubmittedParallel m:type="Edm.Int32" m:null="true"></d:ContractSubmittedParallel>
      <d:BusinessUnit>BA</d:BusinessUnit>
      <d:DocumentReview>review</d:DocumentReview>
      <d:StartDate m:type="Edm.DateTime">2011-08-01T00:00:00</d:StartDate>
      <d:TermNotificationDate m:type="Edm.DateTime">2011-12-30T00:00:00</d:TermNotificationDate>
      <d:Start_Schedule m:type="Edm.Int32" m:null="true"></d:Start_Schedule>
      <d:ScheduleWorkflow m:type="Edm.Int32" m:null="true"></d:ScheduleWorkflow>
      <d:ContractNotes m:null="true"></d:ContractNotes>
      <d:LegalApprovalName m:null="true"></d:LegalApprovalName>
      <d:Id m:type="Edm.Int32">93</d:Id>
      <d:ContentType>Form</d:ContentType>
      <d:Created m:type="Edm.DateTime">2011-08-03T10:25:02</d:Created>
      <d:CreatedById m:type="Edm.Int32">1</d:CreatedById>
      <d:Modified m:type="Edm.DateTime">2011-08-11T20:47:27</d:Modified>
      <d:ModifiedById m:type="Edm.Int32">1</d:ModifiedById>
      <d:CopySource m:null="true"></d:CopySource>
      <d:ApprovalStatus>0</d:ApprovalStatus>
      <d:Path>/departments/legal/contractmgmt/Contracts</d:Path>
      <d:CheckedOutToId m:type="Edm.Int32" m:null="true"></d:CheckedOutToId>
      <d:VirusStatus>52600</d:VirusStatus>
      <d:IsCurrentVersion m:type="Edm.Boolean">true</d:IsCurrentVersion>
      <d:Owshiddenversion m:type="Edm.Int32">13</d:Owshiddenversion>
      <d:Version>1.0</d:Version>
    </m:properties>
</entry>

Re: Fill in repeating table based on multiple selections

Hilary Stoupa — Wed, 15 Feb 2012 17:21:47 GMT

Here's a link to the URI conventions. Here's a link to a demo video I did on OData with SharePoint.

Are you positive that Approver1UserID is the correct object name? Are you positive that the values include 'bsieloff'?

If you use just your link to Contracts in the browser, what do you get back?

I don't quite understand what this means:

bsieloff:
I have been successful in getting information from my new OData(listdata.svc) data connection by just selecting a value for a text box, but when I try to pre-filter things go south.

Re: Fill in repeating table based on multiple selections

bsieloff — Wed, 15 Feb 2012 17:11:13 GMT

IE9 fixed the issue. I did not think it would effect the way the designer/preview worked, but it did.

I have been successful in getting information from my new OData(listdata.svc) data connection by just selecting a value for a text box, but when I try to pre-filter things go south.

My filtered 'OData' data connection in InfoPath looks like:

https://sharepoint.mycompany.com/departments/dept1/contractmgmt/_vti_bin/listdata.svc/Contracts$filter=Approver1UserID eq 'bsieloff'

two things happen.

1) I'm asked to put in my credentials (https thing?)

2) error on the url string. both directly from IE or in InfoPath

Looking at examples on the web, I thought my syntax was correct. Any suggestions?

Thank you