CAML Query Assistance for vLookup

Forums Dynamic Forms for SharePoint CAML Query Assistance for vLookup

This topic contains 2 replies, has 2 voices, and was last updated by  Alexander Bautz 1 week, 3 days ago.

  • Author
    Posts
  • #25649

    Notrega
    Participant

    I have ZERO experience with CAML… but this is the last piece to finish the Shift Report project that you helped so much with.

    I need to use this in a vLookup, but either I get
    Code 1 – Pulls ALL DateV comparison matches but does not filter on Employee
    or
    Code 2 – Pulls only the Employee needed but does not filter on the DateV match

    This is Code 1 – Pulls ALL DateV comparison matches but does not filter on Employee

    
    
    <Where>
    <And>
         <Leq>
            <FieldRef Name='StartDateV_x0023_'  />
            <Value Type='Number'>[currentItem:ShiftDateV_x0023_]</Value>
         </Leq>
    <Geq>
            <FieldRef Name='EndDateV_x0023_'  />
            <Value Type='Number'>[currentItem:ShiftDateV_x0023_]</Value>
         </Geq>
         </And>
    <And>
    <Eq>
            <FieldRef Name='Employee_x002a_0'  />
            <Value Type='Text'>[currentItem:Staff_x0020_Name]</Value>
         </Eq>
        </And>
    </Where>

    This is the Code 2 – Pulls only the Employee needed but does not filter on the DateV match

    
    
         <Eq>
                    <FieldRef Name='Employee_x002a_0' />
                    <Value Type='Text'>[currentItem:Staff_x0020_Name]</Value>
         </Eq>
      <And>
         <Leq>
            <FieldRef Name='StartDateV_x0023_'  />
            <Value Type='Number'>[currentItem:ShiftDateV_x0023_]</Value>
         </Leq>
     
         <Geq>
            <FieldRef Name='EndDateV_x0023_'  />
            <Value Type='Number'>[currentItem:ShiftDateV_x0023_]</Value>
         </Geq>
      </And>
    </Where>
    • This topic was modified 1 week, 4 days ago by  Notrega.
    Attachments:
  • #25658

    Notrega
    Participant

    Forgot to add – the DateV field is the day# of the year… 6/9/2019 = 160
    The DateV is meant to check to see if the ShiftDateV falls between the Start/End DateV

  • #25659

    Alexander Bautz
    Keymaster

    Thanks for feeding my piggy bank!

    To check if a date falls between two dates you must use a CAML like this – using an ISO 8601 date format (Please note that both the tags are in the top):

    <Where>
        <And>
        <And>
            <Leq>
                <FieldRef Name='StartDateV_x0023_' />
                <Value IncludeTimeValue="False" Type='DateTime'>2019-01-01</Value>
            </Leq>
            <Geq>
                <FieldRef Name='EndDateV_x0023_' />
                <Value IncludeTimeValue="False" Type='DateTime'>2019-01-01</Value>
            </Geq>
        </And>
            <Eq>
                <FieldRef Name='Employee_x002a_0' />
                <Value Type='Text'>[currentItem:Staff_x0020_Name]</Value>
            </Eq>
        </And>
    </Where>

    This mean you must get your “ShiftDateV_x0023_” field to hold a date in the above format: 2019-01-01 if you want to use it like [currentItem:ShiftDateV_x0023_] in your CAML.

    Alexander

You must be logged in to reply to this topic.