Adding Field from secondary datasource on startup

[slhangen]

I have an app that uses multiple datasources. 

• The main lookup source is a SHAREPOINT list 'STUDENT DEMOGRAPHIC INFORMATION'
  • This list has around 700 rows and gets updated weekly.
• The secondary source is 'ADAreport' an excel list that gets updated daily.

On startup I want to collect all the fields in 'STUDENT DEMOGRAPHIC INFORMATION' and add a field called Absences from the ADAreport source.  

The common fields are as follows:

'STUDENT DEMOGRAPHIC INFORMATION' key column is OtherID

'ADAreport' key column is Title

Thx community for assisting with this code.

[WarrenBelz]

Hi @slhangen Try this for a start - Free-typed so I cannot test it presently

Clear(colMyCollection);
ForAll(
   'STUDENT DEMOGRAPHIC INFORMATION' As Student,
   Collect(
      colMyCollection,
      AddColumns(
         Student,
         "MyColumnName",
         Lookup(
            ADAreport,
            Title=Student.OtherID
         ).Absences
      )
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[slhangen]

Quite a bit of red here.

Can you elaborate on the blue part:

Clear(colMyCollection);
ForAll( 'STUDENT DEMOGRAPHIC INFORMATION' As Student Collect(ColAddAttendance, AddColumns( Student, "Absences", Lookup( ADAreport, Title=Student.OtherID ).Absences ) ) )

Also customized some field names (ColAddAttendance, Absences) in orange

Hopefully we are getting closer though

[WarrenBelz]

Hi @slhangen ,

As I said, free-typed. I am now back on PC, however this will not work for a couple of reasons I have now worked out.

I have no time at the moment, so will tag a colleague @RandyHayes to have a look.

[RandyHayes]

@WarrenBelz typing freeform...it's our Achilles heal!!

@slhangen 

This bit of formula should do what you need.  This is based on the assumption that there is one row in your Excel with Title equaling the OtherID in the Student Demographic Information datasource.  I also assumed (just based on the naming) that Title was a text column and OtherID is a numeric.

ClearCollect(colMyCollection,
   AddColumns('STUDENT DEMOGRAPHIC INFORMATION' As Student,
       "Absences",
         Lookup(
            ADAreport,
            Title=Text(Student.OtherID)
         ).Absences
   )
)

So, if the above assumptions are all correct, this formula should give you what you want.

IF you have multiple rows in your excel file, then this would be the formula:

ClearCollect(colMyCollection,
   AddColumns('STUDENT DEMOGRAPHIC INFORMATION' As Student,
       "Absences",
         Sum(
             Filter(ADAreport, Title=Text(Student.OtherID),
             Absences
         )
   )
)

Let's see where that takes you.

[slhangen]

Thanks for taking a look Randy.

Just to recap:

• On Start, I am creating a collection from Student Demographic Information (all columns).
  • Only one row per student in this SP list.
• I would like to join a column from the excel file ADAreport to this collection called Absences
  • Also only one row per student in this excel
• Both OtherID and Title are text columns

With re: to your provided code, even after i removed the text container tag, I have lots of red in the code (see below)

Thx again, Randy!

[RandyHayes]

@slhangen 

As @WarrenBelz mentioned...we type these things free-form, so you always have to do some due diligence on any typos we make.  In this case, the Lookup should be LookUp.

Please consider this formula:

ClearCollect(colMyCollection,
   AddColumns('STUDENT DEMOGRAPHIC INFORMATION' As Student,
       "Absences",
         LookUp(
            ADAreport,
            Title=Text(Student.OtherID)
         ).Absences
   )
)

[slhangen]

My apologies, fellas! Still a relative novice trying to get a handle on the verbiage.

Not sure if I have the right and left tables coded correctly. Student Demographic Information is the main table, I just want to borrow the column named DAYS ABSENT from excel sheet ADAreport and add it to the columns of Student Demographic Information into a new collection.

Hopefully we are getting closer but I am still pretty perplexed:

[slhangen]

Only a little red now: Can you identify the error? 

• To recap
  • Column in lookup list Student Demographic Information is a text column OtherID. This is main list.
  • Column in secondary excel sheet to add column from is text column Title

[WarrenBelz]

Hi @slhangen ,

@RandyHayes is probably asleep right now and his solution is correct based on the information you have provided (so please accept his once this is sorted).

Something is not correct in either the field names or types as if they were both plain text (Single line of Text in SharePoint and formatted as text in Excel), then you would not have that error.

Please look at both values with this in mind and see if you can detect the issue.