Nintex Connect

Hello,

I'm new to Nintex workflow and this week I played arround with it. At the moment we are creating a new intranet based on sharepoint technology.

One of the things we would like to implement is room scheduling. The free template from microsoft is not working verry wel so i decided to make one myself with the use of Nintex. The problem  I'm facing now is that I can not find an option to check if a timeslot is allready booked for a resource. Is there a function available which can check if there is another item in the list wich overlaps the current item in date and time ? (condition ?)

Is this possible and how ?

I have created a list with a calender view and added some colums to check against the filled in request (room capacity, coffee/tea). If the number of persons is bigger than the room capacity the iniator gets a message to change or delete his/her request. I also used the option tot update a list item to show pending reserveration or Approved.

It would be great if I get this workflow running.

Thanks in advance

 Edwin ten Haaf

 

You could use a Query List action to design a CAML query that checks to see if there were any items in the calendar match your criteria.

I created a simple workflow that ran on a list item that had 2 date fields - a start and an end date. My workflow checks the Calendar list to see if any items overlap with this date range. I'll admit I copied the actual CAML query from here: http://iguanagears.blogspot.com/2008/09/moss-caml-getting-month-and-date.html, it seems to work as advertised :)

In the Query List action, switch to CAML editor mode and use the following CAML. A couple of notes:

1) {8393F8A6-AEB1-4C30-AEF4-61E3CFF12ABE} is the ID of my 'Calendar' list.

2) {WorkflowVariable:StartCheckISO} and {WorkflowVariable:EndCheckISO} are two text workflow variables. CAML expects the dates to be provided in a particular format, so before the Query List action I have two 'Calculate a Date' actions, that take the date we are querying for from the 'current item', and store it as an ISO 8601 Date string into the text variable, which can then be used in the CAML query.

<Query>
    <Lists>
        <List ID="{8393F8A6-AEB1-4C30-AEF4-61E3CFF12ABE}" />
    </Lists>
    <ViewFields>
        <FieldRef Name="ID" />
    </ViewFields>
    <Where>
        <Or>
            <Or>
                <And>
                    <Leq>
                        <FieldRef Name="EventDate" />
                        <Value Type="DateTime">{WorkflowVariable:StartCheckISO}</Value>
                    </Leq>
                    <Geq>
                        <FieldRef Name="EndDate" />
                        <Value Type="DateTime">{WorkflowVariable:StartCheckISO}</Value>
                    </Geq>
                </And>
                <And>
                    <Geq>
                        <FieldRef Name="EventDate" />
                        <Value Type="StartTime">{WorkflowVariable:StartCheckISO}</Value>
                    </Geq>
                    <Leq>
                        <FieldRef Name="EndDate" />
                        <Value Type="DateTime">{WorkflowVariable:EndCheckISO}</Value>
                    </Leq>
                </And>
            </Or>"
            <And>
                <Leq>
                    <FieldRef Name="EventDate" />
                    <Value Type="DateTime">{WorkflowVariable:EndCheckISO}</Value>
                </Leq>
                <Geq>
                    <FieldRef Name="EndDate" />
                    <Value Type="DateTime">{WorkflowVariable:EndCheckISO}</Value>
                </Geq>
            </And>
        </Or>
    </Where>
</Query>

I store the results into a collection variable, and can then easily check the Count of the collection to see if there were any matches (using a Collection operation) action.

I hope this gives you some ideas

regards,

James 

 

James

 

I have the exact same requirement as above (meeting room). The logic of the formula seems fine but it seem that my workflow is not comparing the date and time but just the date. I have tried your suggestion but still does not work. Any suggestions??

My CAML:

<Query>
    <Lists>
        <List ID="{808D9714-1CA5-4DEB-AA82-39BF22C52792}" />
    </Lists>
    <ViewFields>
        <FieldRef Name="ID" />
    </ViewFields>
    <Where>
        <And>
            <And>
                <And>
                    <Geq>
                        <FieldRef Name="End_x0020_Date" />
                        <Value Type="DateTime">{WorkflowVariable:StartCheckISO}</Value>
                    </Geq>
                    <Leq>
                        <FieldRef Name="From_x0020_Date" />
                        <Value Type="DateTime">{WorkflowVariable:EndCheckISO}</Value>
                    </Leq>
                </And>
                <Eq>
                    <FieldRef Name="Resource" />
                    <Value Type="Lookup">{ItemProperty:Resource}</Value>
                </Eq>
            </And>
            <Neq>
                <FieldRef Name="ID" />
                <Value Type="Counter">{ItemProperty:ID}</Value>
            </Neq>
        </And>
    </Where>
</Query>

I think the issue might actually be that the ISO date is treated as UTC. In the calculate date action, if you add / subtract the hours for you timezone offset hours, does it make a difference?

Nope, that does not work. If I have 2 existing meetings scheduled (say 9-10am and 15-16pm) and I add a new meeting for 11am-12, then the query list action (where I determine if there are existing records) return both of the existing meetings i.e. all the meetings for that day

In the Query List action, if you are looking at your query in CAML editor mode, then you will need to add IncludeTimeValue="TRUE" to the <value> tags that relate to DateTime fields.

NOTE : if you go back to Query Editor mode, then this change will be lost.

Your CAML should look like this

<Query>
    <Lists>
        <List ID="{808D9714-1CA5-4DEB-AA82-39BF22C52792}" />
    </Lists>
    <ViewFields>
        <FieldRef Name="ID" />
    </ViewFields>
    <Where>
        <And>
            <And>
                <And>
                    <Geq>
                        <FieldRef Name="End_x0020_Date" />
                        <Value Type="DateTime" IncludeTimeValue="TRUE">{WorkflowVariable:StartCheckISO}</Value>
                    </Geq>
                    <Leq>
                        <FieldRef Name="From_x0020_Date" />
                        <Value Type="DateTime" IncludeTimeValue="TRUE">{WorkflowVariable:EndCheckISO}</Value>
                    </Leq>
                </And>
                <Eq>
                    <FieldRef Name="Resource" />
                    <Value Type="Lookup">{ItemProperty:Resource}</Value>
                </Eq>
            </And>
            <Neq>
                <FieldRef Name="ID" />
                <Value Type="Counter">{ItemProperty:ID}</Value>
            </Neq>
        </And>
    </Where>
</Query>

Hi,

I have the exact problem.

I was wondering if you found the solution to it?

Thank you,

Huss

HI Vadim,

I have the same problem as discussed in this thread.

I added the IncludeTimeValue="TRUE" to the <value> tags that relate to DateTime fields in CAML editor mode.

after saving it and running the workflow I am getting an error.

can you help me with it.

 

Thank you.

 

 

Hi Hussain,

we might be able to help more if you told us what the error is that you are getting :)

cheers,

Vadim Tabakman

Thanks Vadim.

The error message is:

Error Querying List. Object Reference not set to an instance of an object.

My code is as follows:

<Query>
    <Lists>
        <List ID="{6B46FF16-10A5-4DB6-9DE2-D58D8E07AFAD}" />
    </Lists>
    <ViewFields>
        <FieldRef Name="Location" />
    </ViewFields>
    <Where>
        <And>
            <And>
                <Or>
                    <And>
                        <And>
                            <Or>
                                <And>
                                    <And>
                                        <Leq>
                                            <FieldRef Name="EventDate" />
                                            <Value Type="DateTime" IncludeTimeValue="TRUE">{WorkflowVariable:StartTimeISO}</Value>
                                        </Leq>
                                        <Geq>
                                            <FieldRef Name="EndDate" />
                                            <Value Type="DateTime" IncludeTimeValue="TRUE">{WorkflowVariable:StartTimeISO}</Value>
                                        </Geq>
                                    </And>
                                    <Eq>
                                        <FieldRef Name="Imaging_x0020_System" />
                                        <Value Type="Choice">{WorkflowVariable:TEMP}</Value>
                                    </Eq>
                                </And>
                                <Geq>
                                    <FieldRef Name="EventDate" />
                                    <Value Type="DateTime" IncludeTimeValue="TRUE">{WorkflowVariable:StartTimeISO}</Value>
                                </Geq>
                            </Or>
                            <Leq>
                                <FieldRef Name="EndDate" />
                                <Value Type="DateTime" IncludeTimeValue="TRUE">{WorkflowVariable:EndTimeISO}</Value>
                            </Leq>
                        </And>
                        <Eq>
                            <FieldRef Name="Imaging_x0020_System" />
                            <Value Type="Choice">{WorkflowVariable:TEMP}</Value>
                        </Eq>
                    </And>
                    <Leq>
                        <FieldRef Name="EventDate" />
                        <Value Type="DateTime" IncludeTimeValue="TRUE">{WorkflowVariable:EndTimeISO}</Value>
                    </Leq>
                </Or>
                <Geq>
                    <FieldRef Name="EndDate" />
                    <Value Type="DateTime" IncludeTimeValue="TRUE">{WorkflowVariable:EndTimeISO}</Value>
                </Geq>
            </And>
            <Eq>
                <FieldRef Name="Imaging_x0020_System" />
                <Value Type="Choice">{WorkflowVariable:TEMP}</Value>
            </Eq>
        </And>
    </Where>
</Query>

 

Thank you,

Hussain

 

Hi Hussain,

What version of Nintex Workflow 2007 are you running ?

To check which build of Nintex Workflow 2007 you are running, navigate to the Workflow Designer, drag a workflow action onto the page and configure it or open an existing configured workflow action. At the bottom left of the workflow action's configuration screen is a 5 digit number, the build number.

cheers,

Vadim Tabakman

Hi Vadim,

 

Thanks for your reply.

The 5 digit build number is: 10830

Thanks,

Hussain

 

yeah good question , I came here to see about the exact same thing :)  I'm trying to get rid of a heap of old ASP apps for booking events and the such here.. 

Can't wait to finish gonna be sweet.. Just need to iron out some permission issues with our mail box prior to giving this a shot though..  

 

Hussain.. the next question will be, can you upgrade to the latest version ? :)

That's not funny.  I had already written here that I would recommend an upgrade.  Looks like it didn't go through.  DanS gets bonus points for beating me, even thought I was technologically challenged.

So yeah, Hussain, you should upgrade to the latest release.   The version that is avaiable right now is build 11000.

I finally got the Nintex workflow build version upgrade done (I am on version 11000).

The code is able to successfully detect any overlaps in the meeting.

I am still facing the following problem:

- The code incorrectly deletes meetings during open slots :(

I dont know whats wrong. I am attaching the code below.

Thank you for your help.

 <Query>
    <Lists>
        <List ID="{6B46FF16-10A5-4DB6-9DE2-D58D8E07AFAD}" />
    </Lists>
    <ViewFields>
        <FieldRef Name="ID" />
    </ViewFields>
    <Where>
        <And>
            <And>
                <Or>
                    <And>
                        <And>
                            <Or>
                                <And>
                                    <And>
                                        <Leq>
                                            <FieldRef Name="EventDate" />
                                            <Value Type="DateTime" IncludeTimeValue="TRUE">{WorkflowVariable:StartTimeISO}</Value>
                                        </Leq>
                                        <Geq>
                                            <FieldRef Name="EndDate" />
                                            <Value Type="DateTime" IncludeTimeValue="TRUE">{WorkflowVariable:StartTimeISO}</Value>
                                        </Geq>
                                    </And>
                                    <Eq>
                                        <FieldRef Name="Imaging_x0020_System" />
                                        <Value Type="Choice">{WorkflowVariable:Temp}</Value>
                                    </Eq>
                                </And>
                                <Geq>
                                    <FieldRef Name="EventDate" />
                                    <Value Type="DateTime" IncludeTimeValue="TRUE">{WorkflowVariable:StartTimeISO}</Value>
                                </Geq>
                            </Or>
                            <Leq>
                                <FieldRef Name="EndDate" />
                                <Value Type="DateTime" IncludeTimeValue="TRUE">{WorkflowVariable:EndTimeISO}</Value>
                            </Leq>
                        </And>
                        <Eq>
                            <FieldRef Name="Imaging_x0020_System" />
                            <Value Type="Choice">{WorkflowVariable:Temp}</Value>
                        </Eq>
                    </And>
                    <Leq>
                        <FieldRef Name="EventDate" />
                        <Value Type="DateTime" IncludeTimeValue="TRUE">{WorkflowVariable:EndTimeISO}</Value>
                    </Leq>
                </Or>
                <Geq>
                    <FieldRef Name="EndDate" />
                    <Value Type="DateTime" IncludeTimeValue="TRUE">{WorkflowVariable:EndTimeISO}</Value>
                </Geq>
            </And>
            <Eq>
                <FieldRef Name="Imaging_x0020_System" />
                <Value Type="Choice">{WorkflowVariable:Temp}</Value>
            </Eq>
        </And>
    </Where>
</Query>