We were adding up monetary fields and found that the calculation was giving us to many decimal places. Below you will find the solution to get them back to 2:
Round to 2 decimal places: round((calculation) * 100) / 100
Round to 3 decimal places: round((calculation) * 1000) / 1000
Example:
Formula: round((1.5555*42)*100) / 100
result: 65.33
This is really a method that can be used to also run rules based on whether a user has submitted something before.
Create a single line of text field (Username), make a workflow to set the that field to the username of the person who created the item. Then create a text field (Filter) that you then set its Default Value to a filtered secondary data connection (copy of the original) using the Username as the filter. Then make a rule that looks to see if the Filtered text box is empty or not. If it is empty then the user has note filled out the form before. If it is not empty then the user has filled out the form before.
See Below of step-by-step directions:
Step 1
Create single line of text field (I named mine Username).
Step 2
Create a workflow in SharePoint Designer that will set the Username field to Created By: Login Name
– workflow action ex: Set Username to Current Item:Created By.
Step 3
In InfoPath add the list as a secondary data connection
– make sure Username as one of the fields to grab
Step 5
In InfoPath create new single line of text field (I called mine Filter)
– right click and go to properties or double click your new field
– in Default Value section next to Value: click fx.
– Click Insert Field or Group
– Click Show advanced view
– Under Fields you will see a drop down. Choose the data connection to Secondary one you created above
– Choose dateFields>d:SharePointListItme_RW>Username
– Click Filter Data… at the bottom
– Click Add
– In the first field click Select a field or group…
– Change the Data source to Main
– Choose choose dateFields>d:SharePointListItme_RW>Username
– Click OK
– For the last field choose select field or group
– Choose Username
– Click OK
– Click OK
– you should see Username = Username
– Click OK
– Click OK
– You should see a formula that says Username[Username=.]
– Click OK
– Beside Value you should see a formula that says Username[Username=.]
– Click OK
Step 5
Create a rule who’s condition is set to see if the Filter field is empty or not.
You cannot run a validation rule on a people picker field, however this is what you can do to achieve the same effect:
You can create a single line text field.
Set the default value of the text box to be populated by the people picker field (Display Name, Username (AccountID), or Account Type).
Place the text field into your form
Put a formatting rule that hides this field.
Put a validation rule on that text box.
I also put a section in that had an explanation that had a formatting rule that had the same condition as the validation rule.
So I recently had a request for a title link to open in a new page. I could not find a solution that adequately did what I needed so I came up with my own solution that includes a holder field, a workflow action, and a calculated column.
First create a single line of text field (I called mine “Link Holder“).
Then in SharePoint Designer make a workflow that puts together the html you would like using the Set Field to value action.
ex: Set Link Holder to <a href=’[Encoded Absolute URL]/../Item/displayifs.aspx?ID=[ID]‘ target=’_blank’>[Title]</a>
In the above example I am pulling the main part of the url from the Encoded Absolute URL field, the current item’s ID for the URL and Title for what the link displays. I have added in target=’_blank‘ to make it open in the new page.
After you had done that go back to SharePoint and create a Calculated column.
– Put the Link Holder field in as the formula.
– Set the data type as Number (for some reason this is the only way to make it read as html)
This gives you exactly what you want.
The only issue you will notice is that it is left aligned. This can be fixed by adding a Content Editor Web Part to the page and putting in the appropriate css:
All my cells have a class of ms-vb2 and a <div> with align=”right” so my css in the web part looks like the following:
<style>
.ms-vb2 div {text-align:left;}
</style>
Please note that this aligns all the cells to the left, so if there are cells that you do not want this to happen for, this may not work for you.
All you need to do is add &DefaultView=ViewName to the end or the url.
Ex: https://sharepoint.edu/site/Lists/ListName/Item/editifs.aspx?ID=1&IsDlg=2&DefaultView=Approval
string-length(.) – string-length(translate(., ” “,””)) > 60
In InfoPath, under Fields, Right Click on the single line of text field and choose Field Properties.
Under the Default Value section click the “fx” button beside the blank field.
To extract the day number of a date, use
number(substring(field1, 9, 2))
To extract the month number of a date, use
number(substring(field1, 6, 2))
To extract the year number of a date, use
number(substring(field1, 1, 4))
where field1 represents a date picker control or the function of today() to pull any of those from today’s date.
In a previous post I walked you through how to mount a SharePoint Document Collection as a network drive in Windows. The biggest issue that I have ran into with this is that it can be super slow. There is one setting in Windows that will fix this:
First navigate to the desired site collection.
Then copy the whole URL except /Forms/AllItems.aspx
Example:
https://sharepoint.edu/site/documentCollection/
Depending on your version of window you will the mount a drive and use that url as the drive location. For Windows xp-7 you would follow these directions:
You should now be able to use the document collection as a drive.
You will need to use the Set time action. See below for the workflow example:
Set time as 11:25 for Today (Output to Variable: date)
Then Pause until Vairable: date