search

Kathy-Lueckeman / EASY-Open-Source-Solution

BSD 3-Clause "New" or "Revised" License
34 stars 50 forks source link

How-To Reference a dependent Reference? #11

Open softwareangel opened 5 years ago

softwareangel commented 5 years ago

Greetings,

I am struggling with creating an simple interface that has 70 Institutions (Account lookups) that references over 250 Programs. I did not use the Intended Programs listing since it would be too much for users to scroll a list of 250+ programs; therefore, filtering by an Institution would separate the Program listing significantly.

  1. My first attempt had 2 reference questions with the following queries:

    Question1. SELECT Name FROM Account WHERE RecordType = 'Institution' Question2. SELECT FROM Program WHERE Department_Namec = (SELECT TextValuec FROM QuestionResponse where Question = Question1.ID)

Of course that did not work, since I could not generate a query that would get the value of the previous question's response on the same page.

  1. On my second attempt, I tried a brute-force approach by creating 70 Picklist holding the queries of each Institution:

    SELECT Name FROM Program WHERE Department_Name__c = 'My College Name"

This approach worked for the first 25 widgets/controls, but since there is a 25 widget/control limitation on Visualforce pages, add more generated a failure. Also, I realized the increased storage used, since 70 possible questions for each submitted application was saved with the 1 question response/answer.

  1. Lastly, I tried to store the results in the Application object with hopes of referencing it later:

    Question1. SELECT Name FROM Account WHERE RecordType = 'Institution' (stored in Intended_Institutionc) Question2. SELECT FROM Program WHERE Department_Name__c = :application.Intended_Institutionc (stored in Intended_Program__c)

This approach failed with run-time error stating the variable did not exist. (I tried other variables that I found in the apex classes, which failed also included app, appControl, applicationID, etc.)

I need help. I do not know if Holding Place or URL Parameter usage will help, but I cannot find examples in the User Guide.

Thanks.

kyleschmid commented 5 years ago

@softwareangel Why not use one of the filtering picklists (i.e. School/College)? You could add picklist values for each institution and select the relevant institution on each program. You might need to make a small modification to the area around line 250 in the ApplicationCreate class. By default, EASY doesn't restrict the program list when users pick values and instead displays an error message when they try to create the application with an invalid program. The reason for this is a business one: a common complaint by admissions staff is that users call the office when they can't find the program they want after it's filtered out of the list. There's nothing stopping you from changing this behavior, however.

softwareangel commented 5 years ago

Thanks for your quick feedback. I will investigate and attempt this method. Note that the Institution is also a lookup for the Program | Department__c field and there are common/duplicate Program names such that Alabama and Florida may have a "Master of Psychology" program. To make sure I understand correctly, I am adding the Institution names to the School/College filter of a particular Program. This should reduce my Program list of 270, since there are similar Programs across Institutions. If so, I would need feed the Program filter the Institution name from the question response (which is my initial issue that I am having difficulty tracking).

My interface has

Question 1: Semester - lookup for Academic Term object Question 2: Institution - lookup (or pick list) for Account object Question 3: Program - lookup/filter based for Program object (based on Institution Question 2 Response)

I will let you know if I am successful.

On Tue, May 14, 2019, 6:04 AM Kyle Schmid notifications@github.com wrote:

@softwareangel https://github.com/softwareangel Why not use one of the filtering picklists (i.e. School/College)? You could add picklist values for each institution and select the relevant institution on each program. You might need to make a small modification to the area around line 250 in the ApplicationCreate class. By default, EASY doesn't restrict the program list when users pick values and instead displays an error message when they try to create the application with an invalid program. The reason for this is a business one: a common complaint by admissions staff is that users call the office when they can't find the program they want after it's filtered out of the list. There's nothing stopping you from changing this behavior, however.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/SalesforceFoundation/EASY-Open-Source-Solution/issues/11?email_source=notifications&email_token=AK23UORJR5XYYZZXT5YQ4NTPVKFETA5CNFSM4HMU4TAKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODVK7U3A#issuecomment-492173932, or mute the thread https://github.com/notifications/unsubscribe-auth/AK23UOS5D73T2PLI22P2RYDPVKFETANCNFSM4HMU4TAA .

softwareangel commented 5 years ago

Greetings,

I implemented your suggestion, by using the school/college filter for Institutions. Unfortunately, this revealed another issue with invoking too many queries to the Program object. It appears that Question object/Reference record type invoke queries when the Checklist object is loaded in order to populate the picklist. I assumed there was trigger that did this in real time upon selection (and type-ahead has never worked as advertised, just blank results). When reviewing the interface, the logs show 72 SOQLs invoked, in real-time, the logs show SOQLs exceeding the 101 limitation (with a possible 210 SOQLs for the entire application). I cannot used the available Intended Term/Program feature (ApplicationCreate class - line 250), since this does not allow the applicant to modify at-will according to given requirements; therefore, a mirrored-version that can display in the Checklist was built. I am looking to add a trigger to the Question object so that the Reference record type will populate picklists upon selection. This may impede performance, but will remove the SOQL errors while loading the Checklist.

Thanks for your help.

Raul R. Edwards - smartphone ENOVATIONAL CORP

On Tue, May 14, 2019, 6:42 AM Raul R. Edwards raul@enovational.com wrote:

Thanks for your quick feedback. I will investigate and attempt this method. Note that the Institution is also a lookup for the Program | Department__c field and there are common/duplicate Program names such that Alabama and Florida may have a "Master of Psychology" program. To make sure I understand correctly, I am adding the Institution names to the School/College filter of a particular Program. This should reduce my Program list of 270, since there are similar Programs across Institutions. If so, I would need feed the Program filter the Institution name from the question response (which is my initial issue that I am having difficulty tracking).

My interface has

Question 1: Semester - lookup for Academic Term object Question 2: Institution - lookup (or pick list) for Account object Question 3: Program - lookup/filter based for Program object (based on Institution Question 2 Response)

I will let you know if I am successful.

On Tue, May 14, 2019, 6:04 AM Kyle Schmid notifications@github.com wrote:

@softwareangel https://github.com/softwareangel Why not use one of the filtering picklists (i.e. School/College)? You could add picklist values for each institution and select the relevant institution on each program. You might need to make a small modification to the area around line 250 in the ApplicationCreate class. By default, EASY doesn't restrict the program list when users pick values and instead displays an error message when they try to create the application with an invalid program. The reason for this is a business one: a common complaint by admissions staff is that users call the office when they can't find the program they want after it's filtered out of the list. There's nothing stopping you from changing this behavior, however.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/SalesforceFoundation/EASY-Open-Source-Solution/issues/11?email_source=notifications&email_token=AK23UORJR5XYYZZXT5YQ4NTPVKFETA5CNFSM4HMU4TAKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODVK7U3A#issuecomment-492173932, or mute the thread https://github.com/notifications/unsubscribe-auth/AK23UOS5D73T2PLI22P2RYDPVKFETANCNFSM4HMU4TAA .