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.

[slhangen]

Was able to join my 2 sources with this OnStart of App:

ClearCollect(ColStudentInfoWithAttendance,
AddColumns(
'STUDENT DEMOGRAPHIC INFORMATION',
"Absences",
LookUp(ADAreport, Title = 'STUDENT DEMOGRAPHIC INFORMATION'[@OtherID])));

Now I need to pull that Absence value into another collection, ColAddContact.

On a form, I have a combobox that selects the Student cbSelStudent

OnChange of cbSelStudent:

Set(varSelOtherID, Text(cbSelStudent.Selected.OtherID));
Set(varSelStudentName, Text(cbSelStudent.Selected.StudentName));
Set(varSelStudentLastName, Text(cbSelStudent.Selected.StudentLastName));
Set(varSelStudentFirstName, Text(cbSelStudent.Selected.StudentFirstName));

I then have a button btnAddtoCollect_1 that adds this to collection ColAddContact

btnAddtoCollect_1 OnSelect

ClearCollect(
ColAddContact,
{
ColOtherID: varSelOtherID,
ColStudentLastName: varSelStudentLastName,
ColStudentFirstName: varSelStudentFirstName,
ColContactDate: ContactDatePicker_1.SelectedDate,
ColContactType: ddContactType_1.SelectedText,
ColSELCompetency: ddSELCompetency_1.SelectedText,
ColPresentingIssue: ddPresentingIssue_1.SelectedText,
ColServices: ddServices_1.SelectedText,
ColReferrer: ddReferrer_1.SelectedText,
ColContactName: varSelStudentName,
ColContactMade: varContactMade,
ColNotes: InputNotes_1.Text,
ColAbsences: ColStudentInfoWithAttendance.Absences,
ColContactPerson: varUser}

);

But ColAbsences is returning a table of all the students.

I can't figure out how to grab the Absences value from just the selected student.  

I am thinking about setting a variable in the OnSelect of the cbSelStudent to pull the Absences value.

I would be pulling the absence number from ColStudentInfoWithAttendance for only the student selected from cbSelStudent and patching it to new collection ColAddContact

Utterly perplexed.  Any help is greatly appreciated.

[WarrenBelz]

@slhangen ,

Are you saying your post question is solved?

If so, please mark it as such and post a new thread for the current issue.

[RandyHayes]

Yes @WarrenBelz it was sleep time for me.

So @slhangen, the question would be, what is the Items property of the cbSelStudent combobox?

I am going to assume it is the ColStudentInfoWithAttenedance collection.

If so, then your formula would be much simpler (and working) if you go with this approach.

First - get rid of all the variables in the OnChange - you don't need them.

For your OnSelect of btnAddtoCollect_1 use this formula:

ClearCollect(
    ColAddContact,
        {
            ColOtherID:  Text(cbSelStudent.Selected.OtherID),
            ColStudentLastName: Text(cbSelStudent.Selected.StudentLastName),
            ColStudentFirstName: Text(cbSelStudent.Selected.StudentFirstName),
            ColContactDate: ContactDatePicker_1.SelectedDate,
            ColContactType: ddContactType_1.SelectedText, 
            ColSELCompetency: ddSELCompetency_1.SelectedText,
            ColPresentingIssue: ddPresentingIssue_1.SelectedText,
            ColServices: ddServices_1.SelectedText,
            ColReferrer: ddReferrer_1.SelectedText,
            ColContactName: Text(cbSelStudent.Selected.StudentName),
            ColContactMade: varContactMade,
            ColNotes: InputNotes_1.Text,
            ColAbsences: cbSelStudent.Selected.Absences,
            ColContactPerson: varUser
        }
)

It is much better to reference the control that has the values rather then establish variables as variables are "snapshots" of a point in time.  Even though you are using the OnChange to update the variables, my suggestion is to just avoid them except when absolutely necessary (very few).  In your formula, you reference other control values that way, so why not for the Student name and ID?  I am not sure of the origin of the other variable, but they could be simplified in the same way.

Also, I would re-evaluate the "SelectedText" statements you are using.  Although they continue to work, they are deprecated properties, best to correct them now rather than on the day that Microsoft decides to remove the property all together and your app now breaks.  Since they are based on the signature of the Items of dropdowns, I would not be able to provide the exact syntax without knowing those Item properties.  However, chances are that yourDropDown.Selected.Value will get what you need.  but again, I just speculate on those.

As for the original post and resolution to the formula, you are using the disambiguation syntax for your OtherID.  What was provided should have worked for you.  I only throw a flag of caution as I have seen issues with disambiguation not working as expected.