Infopath 2010 & Sharepoint 2010 rule to update value of textbox from previous update item

Question

I have a project updates list that tracks updates on a list item within the projects list.

I have created an InfoPath form that the Project Manager fills out instead of using a sharepoint built-in form.

I have been requested to auto-populate the last Update's information for the project selected to make it easier for the project managers (i.e: budget didnt change between this week and last week). I took a screenshot of the form and annotated it for easier explanation..

My Problem

my problem is im trying to sort the returned results of the search by DESC order, so it pulls the latest update instead of the very first.

For example: Currently, if i have two previous updates

8/1/12 - $1,000
8/2/12 - $5,000

The form is pulling the $1,000 and not the $5,000 as that is the least greatest.

In SQL id do something like:

SELECT EstimatedBudget FROM projectUpdates pu WHERE pu.Project = ProjectID AND pu.updateDate = updateDate ORDER BY pu.updatedate DESC LIMIT 1;
//which would return one row, 5000

---

The constructed value that the rule sets the estimatedBudget field to is:

EstimatedBudget[Project = Project and msxsl:string-compare(UpdateDate, UpdateDate) <= 0]

Answer 1

Without seeing the xsn, it would be hard to tell you the correct answer. If you can't give up the xsn. The issue can be in several places and not necessary the sorting.

1. Look at the data connection and see if it has all the data; both the $1000 or $5000? If it has both, then it's probably in the filter or fields Estimated Budget that you have to change. If it only has the $1000, then you have to change the data query.

2. If the data contains both, it's possible the rule assigning the value to Estimated Budget needs to be change to something filtering for Last() record or Max date.

The tricky part to the rule is it could be in the Form Load event or on the Estimated Budget field itself.

So, I would look first at your data connection. The the field itself and then the form load event to see what the Estimated Budget gets its value.

Edit:

Looking over your post again, you have some inconsistency. First, you said in your sql, it's returning one row with $5000 but in the form it's returning $1000. So, it's either returning 1 row of $1000 or both. Also you have "=" comparison to the date but in the picture it's <= now().

I don't think you need to even compare the date. You can just pull with the data connection = to project id and get both data and then filter it when assigning using max date or last date...etc...

Answer 2

my problem is im trying to sort the returned results of the search by DESC order, so it pulls the latest update instead of the very first.

There is no need to sort in order to get the latest record, use last() for this

If you want more specific answer, please share your XSN template file