SPO Lookup fields limit

Contributor

Hi All,

 

Trying to query information from some lists, but I think I ended up in the Lookup Fields Limit (12) for SP Online. Anyone have a workaround not to bypass, but to obtain the expected result?

 

Thank you,

Joao

 

{"odata.error":{"code":"-2147024809, System.ArgumentException","message":{"lang":"en-US","value":"Value does not fall within the expected range."}}}

 

 

var camlQuery = {
          ViewXml: 
          '<View Scope=\'RecursiveAll\'><RowLimit>5000</RowLimit>\
            <ViewFields>\
              <FieldRef Name=\'Title\' />\
              <FieldRef Name=\'CertificateNumber\' />\
              <FieldRef Name=\'Validto\' />\
              <FieldRef Name=\'IsPrinted\' />\
              <FieldRef Name=\'PrintedAt\' />\
              <FieldRef Name=\'Date1\' />\
              <FieldRef Name=\'IsInCEIN\' />\
              <FieldRef Name=\'ID\' />\
              <FieldRef Name=\'Ordinal\' />\
              <FieldRef Name=\'Workshop_Title\' />\
              <FieldRef Name=\'WorkshopType_Title\' />\
              <FieldRef Name=\'WorkshopType_Id\' />\
              <FieldRef Name=\'WorkshopType_CertificateDisplayName\' />\
              <FieldRef Name=\'WorkshopLevel_Title\' />\
              <FieldRef Name=\'WorkshopProductLine_Title\' />\
              <FieldRef Name=\'WorkshopDiscipline_Title\' />\
              <FieldRef Name=\'WorkshopDiscipline_Id\' />\
              <FieldRef Name=\'LevelTitle\' />\
              <FieldRef Name=\'LevelShortName\' />\
              <FieldRef Name=\'Participant_Id\' />\
              <FieldRef Name=\'Participant_FirstName\' />\
              <FieldRef Name=\'Participant_LastName\' />\
              <FieldRef Name=\'Participant_Email\' />\
              <FieldRef Name=\'WorkshopCountry_Title\' />\
              <FieldRef Name=\'WorkshopCity_Title\' />\
            </ViewFields>\
            <ProjectedFields>\
              <Field Name=\'Workshop_Title\' Type=\'Lookup\' List=\'Workshop\' ShowField=\'Title\' />\
              <Field Name=\'WorkshopType_Title\' Type=\'Lookup\' List=\'WorkshopType\' ShowField=\'Title\' />\
              <Field Name=\'WorkshopType_Id\' Type=\'Lookup\' List=\'WorkshopType\' ShowField=\'ID\' />\
              <Field Name=\'WorkshopType_CertificateDisplayName\' Type=\'Lookup\' List=\'WorkshopType\' ShowField=\'CertificateDisplayName\' />\
              <Field Name=\'WorkshopLevel_Title\' Type=\'Lookup\' List=\'Level\' ShowField=\'Title\' />\
              <Field Name=\'WorkshopProductLine_Title\' Type=\'Lookup\' List=\'ProductLine\' ShowField=\'Title\' />\
              <Field Name=\'WorkshopProductLine_Id\' Type=\'Lookup\' List=\'ProductLine\' ShowField=\'ID\' />\
              <Field Name=\'WorkshopDiscipline_Title\' Type=\'Lookup\' List=\'WorkshopDiscipline\' ShowField=\'Title\' />\
              <Field Name=\'WorkshopDiscipline_Id\' Type=\'Lookup\' List=\'WorkshopDiscipline\' ShowField=\'ID\' />\
              <Field Name=\'WorkshopCountry_ID\' Type=\'Lookup\' List=\'Country\' ShowField=\'ID\' />\
              <Field Name=\'WorkshopCountry_Title\' Type=\'Lookup\' List=\'Country\' ShowField=\'Title\' />\
              <Field Name=\'LevelId\' Type=\'Lookup\' List=\'Level\' ShowField=\'ID\' />\
              <Field Name=\'LevelTitle\' Type=\'Lookup\' List=\'Level\' ShowField=\'Title\' />\
              <Field Name=\'LevelShortName\' Type=\'Lookup\' List=\'Level\' ShowField=\'ShortName\' />\
              <Field Name=\'Participant_FirstName\' Type=\'Lookup\' List=\'Participant\' ShowField=\'FirstName\' />\
              <Field Name=\'Participant_LastName\' Type=\'Lookup\' List=\'Participant\' ShowField=\'LastName\' />\
              <Field Name=\'Participant_Email\' Type=\'Lookup\' List=\'Participant\' ShowField=\'Email\' />\
              <Field Name=\'WorkshopCity_Title\' Type=\'Lookup\' List=\'City\' ShowField=\'Title\' />\
            </ProjectedFields>\
            <Join Type=\'LEFT\' ListAlias=\'Level\'>\
                  <Eq>\
                      <FieldRef Name=\'Level\' RefType=\'Id\' />\
                      <FieldRef List=\'Level\' Name=\'ID\' />\
                  </Eq>\
              </Join>\
              <Join Type=\'LEFT\' ListAlias=\'WorkshopDiscipline\'>\
                  <Eq>\
                      <FieldRef Name=\'Discipline\' RefType=\'Id\' />\
                      <FieldRef List=\'WorkshopDiscipline\' Name=\'ID\' />\
                  </Eq>\
              </Join>\
              <Join Type=\'LEFT\' ListAlias=\'ProductLine\'>\
                  <Eq>\
                      <FieldRef Name=\'ProductLine\' RefType=\'Id\' />\
                      <FieldRef List=\'ProductLine\' Name=\'ID\' />\
                  </Eq>\
              </Join>\
              <Join Type=\'LEFT\' ListAlias=\'Workshop\'>\
                  <Eq>\
                      <FieldRef Name=\'Workshop\' RefType=\'Id\' />\
                      <FieldRef List=\'Workshop\' Name=\'ID\' />\
                  </Eq>\
              </Join>\
              <Join Type=\'LEFT\' ListAlias=\'Country\'>\
                  <Eq>\
                      <FieldRef List=\'Workshop\' Name=\'Country\' RefType=\'Id\' />\
                      <FieldRef List=\'Country\' Name=\'ID\' />\
                  </Eq>\
              </Join>\
              <Join Type=\'LEFT\' ListAlias=\'City\'>\
            <Eq>\
              <FieldRef List=\'Workshop\' Name=\'City\' RefType=\'Id\' />\
              <FieldRef List=\'City\' Name=\'ID\' />\
            </Eq>\
          </Join>\
              <Join Type=\'LEFT\' ListAlias=\'WorkshopType\'>\
                  <Eq>\
                      <FieldRef List=\'Workshop\' Name=\'WorkshopType\' RefType=\'Id\' />\
                      <FieldRef List=\'WorkshopType\' Name=\'ID\' />\
                  </Eq>\
              </Join>\
              <Join Type=\'LEFT\' ListAlias=\'Participant\'>\
            <Eq>\
              <FieldRef Name=\'Participant\' RefType=\'Id\' />\
              <FieldRef List=\'Participant\' Name=\'ID\' />\
            </Eq>\
          </Join>\
          <were><eq>\
            FieldName Name=\'Participant_Id\' /><Value Type=\'Integer\'>' + id + '</Value>\
          </were></eq>\
        </View>'
        };

        if (Environment.type == EnvironmentType.SharePoint ||
          Environment.type == EnvironmentType.ClassicSharePoint) {
          // // sp.web.lists.getByTitle("WorkshopResult")
          // //   .items.select("Participant/Id, Workshop/Title, Workshop/Date1")
          // //   .expand("Workshop", "Participant")
          // //   .filter("Participant/Id eq '" + id + "'") //+ " and workshop/etestuniquenumber eq '" + un + "'"   )
          // //   .top(5000)
            sp.web.lists.getByTitle("WorkshopResult")
            .getItemsByCAMLQuery(camlQuery)
            .then(items => {

 

 

 

 

0 Replies