CAML Query Assistance for vLookup

Forums Dynamic Forms for SharePoint CAML Query Assistance for vLookup

Viewing 2 reply threads
  • 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 year, 4 months 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

Viewing 2 reply threads
  • You must be logged in to reply to this topic.