cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper V
Helper V

Adding Field from secondary datasource on startup

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.

 

12 REPLIES 12
Highlighted
Super User III
Super User III

Re: Adding Field from secondary datasource on startup

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.

Highlighted
Helper V
Helper V

Re: Adding Field from secondary datasource on startup

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

 

Highlighted
Super User III
Super User III

Re: Adding Field from secondary datasource on startup

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.

Highlighted
Super User III
Super User III

Re: Adding Field from secondary datasource on startup

@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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Highlighted
Helper V
Helper V

Re: Adding Field from secondary datasource on startup

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)

 

slhangen_0-1599609359195.png

Thx again, Randy!

Highlighted
Super User III
Super User III

Re: Adding Field from secondary datasource on startup

@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
   )
)

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Highlighted
Helper V
Helper V

Re: Adding Field from secondary datasource on startup

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_0-1599611455226.png

 

 

 

Highlighted
Helper V
Helper V

Re: Adding Field from secondary datasource on startup

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

 

slhangen_0-1599612810124.png

 

Highlighted
Super User III
Super User III

Re: Adding Field from secondary datasource on startup

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.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors
Users online (5,929)