Alternative to lookup column type in Power Apps?

[WinstonR]

Maybe what I am attempting to do next is quite involved. I have seen bits of what I want to accomplish on the internet, but never the whole. 
I want to build this: drop-down whose choices are imported from another list. Those choices are also filtered based on the value of another column within that list. Then concatenate with the value of another column.

The app I am building is for checking equipment out of inventory. I have another list with job sites (planned, active, inactive.) The goal is to make a drop down in the equipment check-out app and the choices in that drop down be only the titles of the “planned” and “active” job sites concatenated with the name of the site foreman.

to accomplish this do I need to set up multiple data sources to get around the lookup SP column type/effect?

Best regards.  

[TheRobRush]

are these equipment choices present in BOTH the equipment list && the list that has the job site status?

Like you have a list of 6 equipment types, then you only show the ones on the list at sites that are active/planned?

If that's the case you don't really need the first list, just a filter of second list to only show sites active&planned wrapped in a Distinct() for the column you want only one returned item from etc.

If that's not what you meant, send an example of both lists, with some sample info in them, and a picture of what the dropdown should show for the sample data, then I can help you out more specifically

[WinstonR]

Sorry for the late reply. I’m working from extremely remote location . Thank You👍@TheRobRush  I have a lookup column type on the job site list so that you can search and select equipment for site planning purposes, but this action does not “check out” the equipment to this job site. 

I am open to any suggestions. I can work on sending you screen shots this evening. 

[WinstonR]

EquipmentList Form. If possible, I will add a drop-down that contains "case name"&"Team Leader" for all "open" & "not started"

The job site list is fairly straight forward. Columns are essentially job title, location, team leader, startdate, enddate, and then the choice column type (not started, in progress, complete). 

I am wanting to know if it is possible to add a choice/drop-down  on the equipment list form whose options for user to select is only the titles of the "not started" or "in progress" jobs. & if at all possible it would be an added bonus to concat the team leader.full name to the job title. 

I know I'm asking a lot here, but if I could get even a piece of the puzzle I would be greatly appreciative. 

[WinstonR]

---

 wrapped in a Distinct() for the column you want only one returned item from etc.

I have to admit I am too much of a novice to follow the logic. However, I am almost certain that I will need both lists. Job list tracks progress on job sites etc., and the equipment list tracks equip status, maintenance, etc. But I will explore Distinct() a little further. 

[TheRobRush]

Send picture of both lists, just put in a fake record for each and filter to that one only so you dont show us any confidential info. Then I'll take a look at it when I am in the office tomorrow. Also show what youd want the checkout form to look like, picture above just looks like the equipment info, no site checkout info etc

[WinstonR]

Thank you again for the reply @TheRobRush ! 

Please see below pictures of my lists and the form from the equipment list. 

I added a field to the form "assigned to site/team leader" (probably name will change, but getting idea there). In this field is where I have my quandary. I would like to know if it is possile to:

{concat [@Job Site Scheduler], "Job Name" & "Team Leader", if  (status)='not started' or 'in progress')}. 

into the choice column options?

What are your thoughts?

Equipment inventory list with choice column options visible.Job scheduling list (multiple columns that contain data/info are hidden just because they don't need to be visible in the list view). showing choice column options.This is equipment list PowerApps form. I added the (checked out at/with) field

[TheRobRush]

Ok I see you are likely having issues with it because they are choice columns (those can be tricky if you are not familiar with how to process them)

So in this example i am making a dropdown list based on a list with a choice column. ( I am going to assume your choice column only ever has 1 value in it since it is a status. So the First() I call on will alwys return its status.

Now this

///ADDS COLUMN TO CONCAT SITE AND JOB LEADER 1/2
AddColumns(
///FILTER RESULTS OF OUR LIST
 Filter(
///ADDS A COLUMN THAT CONTAINS THE FIRST ITEM IN THE CHOICE COLUMN (JOB STATUS)
   AddColumns(
        ///YOU WILL PUT YOUR JOB SITE LIST NAME HERE
        StorageUnits,
        ///GIVE IT A NEW UNIQUE NAME HERE
        "Test",
        ///YOU WILL PUT YOUR CHOICE COLUMN NAME HERE STATUS OR WHATEVER ITS NAME IS
        First(ContentList).Value
              ),
        ///CHANGE TEST = TO WHATEVER NAME YOU GAVE THE ADDED COLUMN ABOVE, Change "Pavers"         
        ///to your status that you want to show on the list  
   Test="Pavers"
         ),
///ADDS COLUMN TO CONCAT SITE AND JOB LEADER 2/2
             "Contact",
              ////CHANGE THE TWO ContactName/ContactDepartment BELOW TO THE COLUMSN YOU WANT   
              ////TO COMBINE
              ContactName&" - "&ContactDepartment
           )

which gives me a dropdown list of this (only one person matches my code above)

just dont forget to change the dropdown Value to the new column we created, see this

[WinstonR]

Here is what I have. Something is still off.

AddColumns(Filter(AddColumns([@Job Site Scheduler]StorageUnits,”Test”,Progress,First(ContentList).Value), Test=“Not started”),”Contact”.FullName)

I added this expression to the Default of the newly added column. I’m still working it, but I have an expected operator error. 

[TheRobRush]

It's not working because you didn't change the variable in the formula that I notated needed to be changed, you just wrote extra info next to them. You may have to correct the names here because I don't know the actual reference names of your datasource but just going off pictures it should be something similar to the following, where 'Job Site Scheduler' is the list name, status is the job state column, 'Job Name' is the project name and 'Team Leader' is the name to combine with the Job Name... Actual syntax of the code works fine, been tested on my own sharepoint list. Just need to get all your references to your own datasource proper.

//ADDS COLUMN TO CONCAT SITE AND JOB LEADER 1/2
AddColumns(
///FILTER RESULTS OF OUR LIST
 Filter(
///ADDS A COLUMN THAT CONTAINS THE FIRST ITEM IN THE CHOICE COLUMN (JOB STATUS)
   AddColumns(
        ///YOU WILL PUT YOUR JOB SITE LIST NAME HERE
        'Job Site Scheduler',
        ///GIVE IT A NEW UNIQUE NAME HERE
        "Test",
        ///YOU WILL PUT YOUR CHOICE COLUMN NAME HERE STATUS OR WHATEVER ITS NAME IS
        First(status).Value
              ),
        ///CHANGE TEST = TO WHATEVER NAME YOU GAVE THE ADDED COLUMN ABOVE, Change "Pavers"         
        ///to your status that you want to show on the list  
   Test="Not started"
         ),
///ADDS COLUMN TO CONCAT SITE AND JOB LEADER 2/2
             "Contact",
              ////CHANGE THE TWO ContactName/ContactDepartment BELOW TO THE COLUMSN YOU WANT   
              ////TO COMBINE
              'Job Name'&" - "&'Team Leader'
           )

If it doesn't recognize any of these, and they match what is displayed in your splist you will need to go into the splist settings, (gear icon, then click list settings), find the column that is giving you issues, click it, then look in the address bar of your browser, very last string is what powerapps is looking for when referencing your column.

And just to help out on future projects it would be best to keep list and columns names without spaces, so instead of Job Site Scheduler you would go JobSiteScheduler, Job Name becomes JobName etc.