Re: Very simple (hopefully) data lookup issue.

Steve Goodger — Mon, 25 Aug 2014 14:59:37 GMT

There's a few things you can do to harden the process - I went through the same growing pains with my purchase requisitions.

Protect the form - create an InfoPath column called "Status" and promote it to SP - hide it from the view.
In your SP workflow, set the Status to "ReadOnly" or "Edit" - depending on your requirements.
In InfoPath - on form load check the Status name - if ReadOnly switch to another form view that removes Submit button and all fields are readonly.

For document numbering, this was a real brain-teaser and I called on a few other articles to help. Basically - you want to create a counter in your SharePoint table.
I created a numbering system that changed by the date. E.g. 20140825-01, 02, 03 etc (and resets to 01 on the change of a day/month or year) - This required 4 SP columns of data and an InfoPath field.

1. CalcYear - SPColumn - Calculation based = CONCATENATE(YEAR(Modified)) - gives me 2014
2. CalcMonth - same =CONCATENATE(REPT("0",2-LEN(MONTH(Modified))),MONTH(Modified)) - gives me 08 for August (not just 8)
3. CalcDAY - same =CONCATENATE(REPT("0",2-LEN(DAY(Modified))),DAY(Modified)) - gives me 25, again formatted to be 01 for single digit days
4. IndexValue = InfoPath field, single line of text
5. NextIndex = SPColumn = Formula Based = CONCATENATE(REPT("0",2-LEN(IndexValue+1)),IndexValue+1) - this gives me the 2-digit 01,02,03)

Now on InfoPath - my IndexValue field does a data retrieve to get NextIndex[last()] for the default value.
I need to check if a post is happening on the same day (if so increment by 01), or reset the counter.
I do this by comparing the last post in SP, and the current date (Field called PostDate = function "today()" on the InfoPath form) and use a function:

NextNumber = concat(substring(IndexValue, 1, (contains(LastPostDate, PostDate)) * string-length(IndexValue)), substring("01", 1, (not(contains(LastPostDate, PostDate))) * string-length("01")))

Note you may have to do some formatting to ensure they both look the same "MM-dd-yyyy"
I also get rid of the InfoPath form submit button (and remove it from all views and permissions) and create my own custom submit button with rules - it will:

Validate the fields
Set the Status column to ReadOnly
Calculate the Document Number based on the above - important to create the number on POST, to avoid conflicts with multiple users.
=concat("REQ-", "-", translate(PostDate, "-", ""), "-", NextNumber) - gives me REQ-20140825-01,02,03, then REQ-20140826-01,02...
Post the document to SP

It's a lot to digest in a short forum post, but here is an article that may help you where my descriptions fail:
http://dlairman.wordpress.com/2011/01/10/add-a-unique-auto-incrementing-column-to-a-sharepoint-list/

Cheers
Steve

Very simple (hopefully) data lookup issue.

themunga — Mon, 25 Aug 2014 00:08:17 GMT

Hi everyone,

I need help with a specific step that I am trying to get past in my InfoPath form, and I would like to do it without using code. Basically I have an email form that does the following:

Standard user fills in a form from SharePoint
Form is then emailed to an admin user based on selection criteria in step 1 (standard user has now "finished" their part)
Form is either approved or not approved by an admin user after opening the InfoPath file in Outlook.

What I would like to do is have the form inaccessible after step 3 has been completed. Because of the way that email forms work, the state of the form stays the same as when it was submitted and the admin user is able to re-open the form from Outlook even if they have "completed" the form (I have not found anything to the contrary so please enlighten me if I am wrong). Re-opening the form should not be able to happen for various reasons. I have tried to make this solution:

Generate a unique number (don't recalculate so number is static) when form is loaded
When the admin user clicks "Approve", set a rule to submit the document to a SharePoint library with the unique number as above
If form is loaded again, check the Sharepoint library for a file with the same name as the unique number and if it matches, display a message and close the form before any inputs can be entered.

Biggest issue here is that I can't query SharePoint for the "Name" field in the document library through the normal InfoPath connection wizard so I have to do a SQL lookup on all documents in SharePoint, and I am having issues trying to get a query that goes "Check SQL server for this document name" and putting it into a rule. Can anyone help me with a solution?

Thanks everyone.

General

Re: Very simple (hopefully) data lookup issue.

Very simple (hopefully) data lookup issue.