cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
slhangen
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
WarrenBelz
Super User III
Super User III

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.

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

 

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.

@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.
Check out my PowerApps Videos too!

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!

@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.
Check out my PowerApps Videos too!

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

 

 

 

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

 

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
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Apps Community Call

Monthly Power Apps Community Call

Did you miss the call?? Check out the Power Apps Community Call here!

secondImage

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Top Solution Authors
Top Kudoed Authors
Users online (77,836)