Formatting Infopath Formula results in Forms Library column? 1

[jkellington]

I have an Infopath form published to a WSS 3.0 forms library for the purpose of entering and tracking purchase orders. I have the key fields in the PO form publishing themsleves to Sharepoint for use in other places in the site.

The problem I have is formatting my currency values in the list of entries in the Sharepoint library. The Infopath form has a POTotal field (calculated) that shows the total dollar value for the PO. It's formatted to show as currency to two decimal places (via the formatting functionality in Infopath).

Now when this value shows up in the Sharepoint column, neither the currency nor the decimal place formatting is applied. Because it is a value passed to Sharepoint from the form, I don't have the normal formatting options I would if this was a standard Sharepoint-created column.

COuld anyone provide me with a way to accomplish some formatting within my Sharepoint library so I don't have dollar values showing up to 4 and 5 decimal places? (The currency symbol would be nice ,too )

Thanks,
Jason

 

[Beowulf005]

In the libary create a new column with a similar name.
Make it a calculated column
Select the column name that has the number you want
Select currency with the formating you want

In the views
Remove the infopath column
Add the column you just made to the view

It would be nice for sharepoint to allow formating of infopath columns but this works around it.

HTH

When frustrated remember, in the computer world there is almost always a backdoor.

 

[jkellington]

I can't believe I didn't think of that! It's been a long week.

Thanks so much,
Jason

 

[tmf]

Hello,

I am creating a custom list that contains some information of when a customer call is recieved and completed. What I want to do is to create a column that contains the number of days that the call has been in the system unresolved.
I have a date entered in the system field and a date field that contains the current date. What I need is to have a column that contains the difference between the two dates in number of days. This way I can use the list to
create a visual KPI for one of my managers.

The problem I am having is in learning how to create a dynamic value in the column that contains the difference between to two dates each time the list is accessed.

I managed to discover a link by Mark Krugar in which he writes that you can wrtie a formula in the provided box below the defualt value (Number or Calculated Value).

My 2 columns are:
SubDate - submitted date
Date - todays date
No. of days - value of date difference

His formula for calculating # of days is:
= Date - SubDate

However I get an error back from the server that says:
The formula contains reference(s) to field(s).

Anyone know how to rectify this?

 

[MaxEd]

Try DATEDIF(Date1,Date2,"D").

-Laughter works miracles.

 

[Beowulf005]

Try changing te formula to this

[Date]-[Subdate]

However if the Date field is a field that defaults to [today] that is most likly where your error is coming from. Change it to an imput date.
Also have you tried using a formula of

[Created]-[Modified]

This will display the number of days since the list item was created to the day it was modified.

HTH

When frustrated remember, in the computer world there is almost always a backdoor.

 

[MaxEd]

As Beowulf005 said your calculation for "Date - todays date" is wrong that's why you are getting that error.

If you are looking to use TODAY's date for your calculation here's the trick.

1. Add the column called TODAY (leave everything default).
2. Add the column you are going to run the calculation.
3. Delete the TODAY column.

Shazam. It's done!

Don't ask me how I know cuz someone else did it first. I searched high and low before running across this. It works in v2 and v3. Have fun!

-Ed


-Laughter works miracles.

 

[tmf]

BeoWulf005 and Ed,

Thank you both for your comments.

My first question is, in MOSS2007 is there a special area to enter the actual formula? In MOSS2003 you were able to enter formulas in an advanced options panel but in MOSS2007 it appears that this panel no longer exists. There is a small text box under Default Value but I'm not sure that is what this is for.

If I put BeoWulf005's formula [Created] - [Modified] I get an error back stating "Default Value is not a vaild number" even though I have Calculated Valued checked. All other formulas still return previous error about using column reference.

Ed, if I follow your instructions as you have written, I still cannot get this to work. The reason I figure I cannot get this to work is because the formula still fails when I attempt to save the column before deleting the Today column.

Thank you both for your help.

Sincerely,

Tim

 

[MaxEd]

Here's an example of the formula in use.

=IF([SubDate]="","",(DATEDIF(Today,[SubDate],"D")))

-Laughter works miracles.

 

[tmf]

Ed,

Can you tell me what version of MOSS you are using? I still get an error indicating that the value entered is not a number.

If you are using MOSS 2007(V3.0) can you confirm for me that I am entering the formula in the correct box?

Thank you again for your help.

Tim

 

[Beowulf005]

Also make sure you are using a calculated column, not the default value of a date column.

When frustrated remember, in the computer world there is almost always a backdoor.

 

[tmf]

Here is the setup of the three columns in use:

Today - default (as per instructed)
Date - date/time format (date only)
Days - number - calculated value with the following formula in the box just below:

=IF([SubDate]="","",(DATEDIF(Today,[SubDate],"D")))

I get an error indicating that The formula contains reference(s) to field(s).

I can't get past this. I assume that I can't delete the Today column until this column is saved.

Thank you again.

Tim

 

[Beowulf005]

Try this

Create a Date Column called Begin.
Create a Date Column called End
Create a Calculated Column Called Lenght and make the value equal [end]-[begin]
This will show how many days from Begin to End.

My Example

When frustrated remember, in the computer world there is almost always a backdoor.

 

[MaxEd]

Rename your DATE to "SubDate" because you haven't created a column called SubDate.


-Laughter works miracles.

 

[tmf]

YES!!!

As I suspected it was a matter of being in the right spot. As I look down the list of options in creating the column, I didn't look far enough down the list to see calculated in the top list. I was looking down at the buttom of this configuration panel under default data type.

I will now attempt to try the use of this formula:
[Created]-[Modified] since I would rather not have human intervention in posting dates.

Thank you very much for all your help (both of you)!

Sincerely,
Tim

 

[tmf]

Beowulf005 and Ed,

Ok, as it has been discussed in many forums the main thing lacking in this functionality is the ability to have the current date be incrememented +1 for every day it is open dynamically.

Currently the forumula is written to equate the difference between the create date (entered when a user creates a new record) and the modify date (referenced when a user changes a record). This however does not really accomplish the task [to reveal the number of days between when a record is created and the current date].

Is there any way to query the server for the current date in Beowulf005's formula rather than using the modify date?

The current formula:
=Modified-Created

Thank you for your thoughts.

Tim

 

[Beowulf005]

You cannot use dynamic values in the calculated columns.

Ok correct my if I am wrong but I belive you are trying to come up with a system to view unresolved customer calls over a given time period. IE 1 Week, 2 Weeks, etc ,This can be accomplished by using different views instead of columns. (with a little help from columns)

Example I want to create a display that shows unresolved tickets that have been open for less then one week, and another for items open more than one week.

Columns
Title (single line text)
Customer problem (multi line text)
Closed (Yes/No)
1 week (Calculated) Value=[created]+7 as date

Create a view called Less than 1 week where it is filtered by
[Closed] = No
and
[1 week] = less than or equal to Value=[today]
Sorted by [Created]

Create a 2nd view called More than 1 week where it is filtered by
[Closed] = No
and
[1 week] = Greater then Value=[today]
Sorted by [Created]

You have just about any combination you want with this.

HTH
Brian

When frustrated remember, in the computer world there is almost always a backdoor.