Finding Queue Position/Reading Row Position From 1 DVWP to another DVWP

May 19, 2014 at 5:04 PM
Hi Everyone,

Hoping you can help me with something I'm having trouble with...

Basically I've got a list (Lets call it MainList) that holds orders, when an item is created, it's a "new order", when a back end user picks it up it becomes "in progress" and when they have finished with it, it becomes "completed" - fairly straight forward so far...

Many different companies can submit orders, and on the home page I have 2 views from this MainList, "My Open Orders" (new order) & "My Pending Orders" (in progress) which are restricted on created by and the appropriate status, so, when a company logs into the site they can see what orders they've placed and what their current status is.

So, the MainList always has every item in it and there's another view (Called BackEnd) that I've created for the back end team, now that view is restricted so that when the status is changed to "Completed" items drop off the view and I've sorted it by date so they get the oldest at the top - this gives them a queue to work.

My problem is that I've been given a requirement to show the companies on the home page where their orders actually sit in the grand scheme of things, so if we focus on Pending (in progress) orders, there could be lots of orders with a status of "in progress" but some companies may have submitted in between the submissions of other companies, so we'd have something like:

MainList

Company1 | Item1 | In Progress
Company2 | Item1 | In Progress
Company1 | Item2 | In Progress
Company2 | Item1 | In Progress
Company3 | Item1 | In Progress

That means that right now, Company2's Item1 is actually the 2nd item in the work queue, Company3's 1st Item is 5th in the queue etc.

Now on my homepage I have the "My Pending Orders" view, what I want to do is feed that information back to the companies, so for Company1 their homepage would look something like:

My Pending Orders

Item1 | In Position 1
Item2 | In Position 3

The only way I can think to do this is by swapping out the existing list view and dropping in a data view web part, and somehow calculate the position by using an index and row counts etc or by creating a separate "hidden" DVWP that filtered and link to it reading the position of the item in that list into the DVWP I'd display on the home page - I'm open to any suggestions though.

Things I've tried:

To show current position in dataview

'<xsl:value-of select="position()" />'

To attempt to calculate position in list
<td class="ms-vb">
<script language="javascript" type="text/javascript">
function LastItemID(){
var lastId = $().SPServices.SPGetLastItemId({ 
listName: &quot;Business Gateway Order&quot;
});
var currID = &apos;<xsl:value-of select="@ID" />&apos;
var rowTot = &apos;<xsl:value-of select="count(/dsQueryResponse/Rows/Row)" />&apos;
return lastId - currID + rowTot
}
</script><script type="text/javascript">document.write(LastItemID());</script></td>
There were others but they were miles off so I won't post them here, I've wittered on for long enough too I think! I feel like I'm getting close, but nothing gives me quite what I need, also I've spent a lot of time reading various links and examples and to be honest I'm running out of ideas.

Cheers
thepurpledongle
Coordinator
May 20, 2014 at 2:14 PM
Wouldn't it work to just use an OrderBy in the CAMLQuery that returns the items in the order you need? i.e., by ID (descending)?

M.
May 20, 2014 at 2:23 PM
sympmarc wrote:
Wouldn't it work to just use an OrderBy in the CAMLQuery that returns the items in the order you need? i.e., by ID (descending)?

M.
erm... I'm not sure - I'll explore how that would work and then come back shortly, thanks for the advice.
May 20, 2014 at 4:20 PM
Ok, I'm not quite sure I understand...

So I've been reading up on GetListItems & CAMLQueries and I can see how you'd bring back an ordered list - but how would I feed in the row position and relate that to the correct row/item in the pending list?

Apologies if that seems really obvious/me being thick!
Coordinator
May 20, 2014 at 4:26 PM
I may be missing the complexity somehow. It seems as though you want to just filter based on who the current user is. If they belong to Company A, you show them Company A data. Company B, Company B data. Can you just filter and sort to show the data you want? I'm not sure I understand why you need two Web Parts.

M.
May 20, 2014 at 4:32 PM
Apologies - It's probably my explanation. Potentially I don't need two lists, that's just where my thought process has taken me.

The complexity lies in (Or at least I feel it's complex) not only showing Company A data to Company A etc, but also where they sit in the overall queue... So say Company A submitted 3 orders into the MainList, but the MainList already had 10 orders in, if the back office works them oldest to newest, Company A's orders would drop into the work queue in positions 11, 12 & 13 - I want to report this back to Company A on the front page of my site, so they know where they sit overall.

...is that a little clearer?
Coordinator
May 20, 2014 at 4:42 PM
So isn't it just two different filters? (But would you want Company A to see anything about Company B? That seems odd.)

M.
May 20, 2014 at 4:50 PM
Yeah, I would filter the view on the front page so companies could only see their own orders.

Unless I'm missing the obvious I don't think it's two filters - or at least I can't think how you'd get that effect with filters as what would be the position field isn't actually stored anywhere.

The position in the queue needs to change as other items get completed and it moves up to the top of the MainList but be reported out on a different list/view showing only the specific items relating to an individual company - it's the reporting of the position relative to the items in the MainList that's the issue.
Coordinator
May 20, 2014 at 10:24 PM
It still seems to me that if you filter for the company and then display the items in ID (or Created) order, you'll have what you need.

M.
May 21, 2014 at 6:32 AM
Edited May 21, 2014 at 9:39 AM
Yep, I see what you mean, that would put the items in the order they would be dealt with for the specific companies. but do you think it's possible to display an extra derived/dynamic field column next to these items with a value showing the actual position in the MainList do you think?

Like in my example above, #1, #2 etc being row position:

MainList

#1 | Company1 | Item1 | In Progress
#2 | Company2 | Item1 | In Progress
#3 | Company1 | Item2 | In Progress
#4 | Company2 | Item1 | In Progress
#5 | Company3 | Item1 | In Progress

And the view for Company 1 would be like:

My Pending Orders

#1 | Company1 | Item1
#3 | Company1 | Item2
May 21, 2014 at 10:03 AM
Actually, I think I may have a way forward - if my code worked!

I figure I can drop this code into a data view column that holds the same info as MainList to write the postion to the list when the page is refreshed:
            <td class="ms-vb" style="width: 51px">
                  <script language="javascript" type="text/javascript">
                  currID = &apos;<xsl:value-of select="@ID" />&apos;
                  currPos = &apos;<xsl:value-of select="position()" />&apos;
                    $(document).ready(function() {
                        $().SPServices({
                            debug: true,
                            operation: "UpdateListItems",
                            async: false,
                            batchCmd: "Update",
                            listName: "Business Gateway Order",
                            ID: currID,
                            valuepairs: ["jsPosition", currPos],
                            completefunc: function(xData, Status) {
                            alert(xData.responseText)
                            alert(Status.responseText)
                            }
                        });
                        });
                </script></td>
Then directly underneath have another dataview that is filtered by company sorted by the new "jsPosition" column. The only thing is I can't get this bit of code to run, xData returns an error about the key already existing and status returns 'undefined' - ANyone have any ideas on where I'm going wrong with this?

In the HTML it seems to be working as I expected, but it's not updating the actual list column - no surprise I gues with the errors.

HTML for reference:
<script language="javascript" type="text/javascript">
                  currID = '4'
                  currPos = '1'
                    $(document).ready(function() {
                        $().SPServices({
                            debug: true,
                            operation: "UpdateListItems",
                            async: false,
                            batchCmd: "Update",
                            listName: "Business Gateway Order",
                            ID: parseInt(currID),
                            valuepairs: ["jsPosition", currPos],
                            completefunc: function(xData, Status) {
                            alert(xData.responseText)
                            alert(Status.responseText)
                            }
                        });
                        });
                </script><script language="javascript" type="text/javascript">
                  currID = '5'
                  currPos = '2'
                    $(document).ready(function() {
                        $().SPServices({
                            debug: true,
                            operation: "UpdateListItems",
                            async: false,
                            batchCmd: "Update",
                            listName: "Business Gateway Order",
                            ID: parseInt(currID),
                            valuepairs: ["jsPosition", currPos],
                            completefunc: function(xData, Status) {
                            alert(xData.responseText)
                            alert(Status.responseText)
                            }
                        });
                        });
                </script><script language="javascript" type="text/javascript">
                  currID = '6'
                  currPos = '3'
                    $(document).ready(function() {
                        $().SPServices({
                            debug: true,
                            operation: "UpdateListItems",
                            async: false,
                            batchCmd: "Update",
                            listName: "Business Gateway Order",
                            ID: parseInt(currID),
                            valuepairs: ["jsPosition", currPos],
                            completefunc: function(xData, Status) {
                            alert(xData.responseText)
                            alert(Status.responseText)
                            }
                        });
                        });
                </script><script language="javascript" type="text/javascript">
                  currID = '7'
                  currPos = '4'
                    $(document).ready(function() {
                        $().SPServices({
                            debug: true,
                            operation: "UpdateListItems",
                            async: false,
                            batchCmd: "Update",
                            listName: "Business Gateway Order",
                            ID: parseInt(currID),
                            valuepairs: ["jsPosition", currPos],
                            completefunc: function(xData, Status) {
                            alert(xData.responseText)
                            alert(Status.responseText)
                            }
                        });
                        });
                </script><script language="javascript" type="text/javascript">
                  currID = '8'
                  currPos = '5'
                    $(document).ready(function() {
                        $().SPServices({
                            debug: true,
                            operation: "UpdateListItems",
                            async: false,
                            batchCmd: "Update",
                            listName: "Business Gateway Order",
                            ID: parseInt(currID),
                            valuepairs: ["jsPosition", currPos],
                            completefunc: function(xData, Status) {
                            alert(xData.responseText)
                            alert(Status.responseText)
                            }
                        });
                        });
                </script><script language="javascript" type="text/javascript">
                  currID = '10'
                  currPos = '6'
                    $(document).ready(function() {
                        $().SPServices({
                            debug: true,
                            operation: "UpdateListItems",
                            async: false,
                            batchCmd: "Update",
                            listName: "Business Gateway Order",
                            ID: parseInt(currID),
                            valuepairs: ["jsPosition", currPos],
                            completefunc: function(xData, Status) {
                            alert(xData.responseText)
                            alert(Status.responseText)
                            }
                        });
                        });
                </script> <script language="javascript" type="text/javascript">
                  currID = '11'
                  currPos = '7'
                    $(document).ready(function() {
                        $().SPServices({
                            debug: true,
                            operation: "UpdateListItems",
                            async: false,
                            batchCmd: "Update",
                            listName: "Business Gateway Order",
                            ID: parseInt(currID),
                            valuepairs: ["jsPosition", currPos],
                            completefunc: function(xData, Status) {
                            alert(xData.responseText)
                            alert(Status.responseText)
                            }
                        });
                        });
                </script>
May 21, 2014 at 11:02 AM
I realise I'm talking to myself here but feel I should update on progress...

I figured out why my code was giving an error, I changed this:
valuepairs: ["jsPosition", currPos],
To this:
valuepairs: [["jsPosition", currPos]],
And it does in fact update the list, but it only updates the last item in the list with the position and leaves the others empty, so I just need to work out why that is happening and I should be away.
May 21, 2014 at 1:13 PM
Nailed it - Thanks for all your help marc....
<td class="style2">
                <script language="javascript" type="text/javascript">
                    $(document).ready(function() {
                        $().SPServices({
                            debug: true,
                            operation: &quot;UpdateListItems&quot;,
                            async: false,
                            batchCmd: &quot;Update&quot;,
                            listName: &quot;BGO&quot;,
                            ID: &apos;<xsl:value-of select="@ID" />&apos;,
                            valuepairs: [[&quot;jsPosition&quot;, &apos;<xsl:value-of select="position()" />&apos;]],
                            completefunc: function (xData, Status) {
                            }
                        });
                        });
                </script> </td>
Then I read the jsPosition column in another view.
Coordinator
May 21, 2014 at 3:19 PM
Glad you solved it, one way or another!

M.