A true challenge

[nodice]

Hi all,

    I have a doozy for community.

I have 2 lists: 'Course Catalogue' and 'Training Database'

Users fill out an edit form that pulls course data from 'Course Catalogue' and submits the data to 'Training Database'

In the training database there is a field for "Status" which is pass, fail, in progress.

I have a gallery that filters the course catalogue by course and allows you do go deeper into a view form on specifics about that course. Would it be possible to lookup if the current user has completed that course and show their status on the view form. Something like "You cave completed this course" 

What im having trouble with right now is since the gallery is showing the course catalogue I need to search that particular course in the training database list for that particular user and return a field. I'm hoping there's some discrete way to go about this to make it easier but im currently at a loss. Any help would be appreciated

[Ethan_R]

Hi @nodice ,

If you are trying to check a course related to a specific User and also linked to the Training Course then you need to use to filter conditions:

Following is the syntax,

Filter(
   'Training Database',
   Course = Gal_Course.Selected.Course && 'Assign User Column'.Email = Gal_User.Selected.Email
)

Note the following:

1. I considered Course is your Column name in Training List and Gal_Course is your 1st Gallery where I selected Course as primary ID or unique ID since its lookup.

2. 'Assign User Column' will be your Column name in the 'Training' List so replace accordingly where Email is the User's property that I'm using to identify. If you are going with Name is fine. 

3. I assumed you are used Gallery for your User selection but you can also use Dropdown or combobox.

Hope this helps

[developerAJ]

you can use

If(IsBlank( Lookup(Training Database,CourseName=gallery.selected.coursename &&'Created By'.Email=User().Email,Status)),"You have not started Training","You started Training and Status is "&Lookup(Training Database,CourseName=gallery.selected.coursename &&'Created By'.Email=User().Email,Status))

Please click Accept as solution and give it Thumbs Up 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.

You can connect with me on LinkedIn

[nodice]

Its not giving me the option to select the CourseName column in the lookup. Its only giving me datacard choices. One from my editform and one from the form viewer. Should I link it to the form viewer? Not sure that makes sense data-wise though. 

This would also be helpful because the gallery will automatically take them to their submission on that course so they can go right to their past data on that course

What if, and this is a strange if, I create a second gallery on the screen and just return the data that way? 

Heres my line. Its not properly filtering by course though:

Filter('AS Employee Training Database',Learner.Email=currentUser || 'Course Specific' =Label10.Text)

Course Specific is basically course name and Label10 is where on the view form the course name is displayed

[nodice]

Alright sorry for the double reply but I've been able to follow your suggestion up until 

&Lookup(Training Database,CourseName=gallery.selected.coursename &&'Created By'.Email=User().Email,Status))

Then it gives me an error when it comes to the & symbol. 

[Rajkumar_404]

Hi

Plz try this formula 

Filter('AS Employee Training Database', Learner.Email = User().Email && 'Course Specific' = Label10.Text)

Thanks!

If my response has been helpful in resolving your issue, I kindly request that you consider clicking "Accept as solution" and "giving it a thumbs up" as a token of appreciation.

[developerAJ · Accepted solution]

if your status is choice columns use Status.Value

&Lookup(Training Database,CourseName=gallery.selected.coursename &&'Created By'.Email=User().Email,Status) is to get status

if you want you can remove it and try only text

If(IsBlank( Lookup(Training Database,CourseName=gallery.selected.coursename &&'Created By'.Email=User().Email,Status)),"You have not started Training","You started Training ")

Please click Accept as solution and give it Thumbs Up 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.

You can connect with me on LinkedIn