Skip to main content
BOOK A DEMO

Search

Creating a "Currently Available" column

Answered
Jeremy Johann
  • Discussion Created

Hello,

I am creating a basic site with the purpose of scheduling to use a company truck, and to see whether it is currently available or not. 

I currently have my main dossier, which has one Item that opens a display form. Opening this form shows the page below: This form contains a sub-list called "Truck Calendar", which has the columns; Check In date, Check out Date, Employee, and the Current date (The date the item was added).

I need to figure out a way for the "Currently Available" column to update based upon whether the present time of viewing the form collides with a scheduled time someone has setup to use the truck, and if so, mark it as not currently available.

0

Comments

6 comments

Date Votes
  • JayT

    I have a similar solution I'm trying to build. Anyone have any input on this?

    0
  • Christof

    You have to check if there are items on your Truck Calendar list which are conflicting with current date.
    Therefore you can use the query list function with a filter getting back the number of conflicting items.
    If your "Currently Available" column is of type "Yes/No" you can use the following Initial/Calculated Assignment Expression on your New/EditForm - or use this same Expression for Visibility check of a Rich Text control on the form (possible as well directly on DispForm):

    [[@Web.QueryList('Name of your Truck Calendar List', '<Where>' +
        '<And>' +
            '<Leq><FieldRef Name="Internal Name of your Check In Date Column" /><Value Type="DateTime" IncludeTimeValue="TRUE">[[= new Date().format("yyyy-MM-ddTHH:mm:00Z") ]]</Value></Leq>' +
            '<Geq><FieldRef Name="Internal Name of your Check Out Date Column" /><Value Type="DateTime" IncludeTimeValue="TRUE">[[= new Date().format("yyyy-MM-ddTHH:mm:00Z") ]]</Value></Geq>' +
        '</And>' +
    '</Where>', 0, 'ID,Title', '')]].length < 1
    Replace in the expression these three parts...
    - Name of your Truck Calendar List
    - Internal Name of your Check In Date Column
    - Internal Name of your Check Out Date Column
    ...with your list's display name and column internal names (best use the Expression Builder getting the correct internal names of columns).

    Let me know if that works in your case
     
    0
  • Jeremy Johann

    I attempted the code provided and am struggling to debug an error I am receiving.

    As you can see above the list does exist on the site. However, after attempting to try the script provided, I'm met with the error saying the list does not exist.

    I modified the variables necessary as seen below:

    At the bottom you can see the issue I am running into. Any ideas? Thanks.

    0
  • Christof

    You have to use the displayed name "Truck Calendar" in your case (or relative url of the list). Check the list parameter on the query list function reference: List Name (usual name, not internal one): for lists from the current site.

     

    0
  • Jeremy Johann

    Thank you very much for your response and your solution worked perfectly for what we needed.

    Another question, is there a way to add column validation to see if dates overlap before a user adds the item? Just to make sure someone doesn't schedule a date that is already in use on the list.

    0
  • Christof

    You can use the same expression checking the conflicts and paste it into the Validation of your desired column on the form like this:

    If the Validation check is negative: The Validation Text will be shown below the field  and the form can't be saved. 

    0

Please sign in to leave a comment.