Using a Set(var) formula with Lookup when SP List column is blank

[Brian_S]

Hello!  I am hoping someone can assist me.  I have a PowerApps form that I am having fields auto populate by using a Set(var) formula with a Lookup function.  I am a rookie in PowerApps and through hours of research found this formula and got it working - don't ask me how :).  The formula works great when the column cells in the SP List contains information, but there's one column (titled "Workstream Supported - a dropdown on the MSL_Master SP List, but I have it as a text box on the form), that has some cells that are blank.  I have tried using many IsBlank formulas, but nothing is working.  Below is the formula that is working for another cell.  Can someone please assist me with a formula I can use to return a blank value to the "Workstream Supported" text box if not data is present, and return information if data is in the cell?  

Set(varLabor,LookUp(MSL_Master,'Name (Title)'=drpName.Selected.Value,'Labor Category'));

MSL_Master:  Is the SharePoint List Data source

Name (Title):  Is from the MSL Master data source and the main column I am using to autopopulate everything with

drpName:  Is the name of the drop down on the form I am using

'Labor Category:  Is a column from MSL_Master list that is working and will replace 'Labor Category' with 'Workstream Supported'

Thanks in advance for your help - everyone has been so awesome with help on this board.

[WarrenBelz]

Hi @Brian_S ,

I am a little unclear on the outcome required if the LookUp is blank, but the structure should be like this

Set(
   varLabor,
   Coalesce(
      LookUp(
         MSL_Master,
         'Name (Title)' = drpName.Selected.Value,
      ).'Labor Category',
      YourOtherValueHere
   )
);

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.

MVP (Business Applications)   Visit my blog Practical Power Apps

[Brian_S]

Thank you so much sir.  Would "YourOtherValueHere" be "", if I want the result to show a blank field if no data is present as a result of the formula?

[WarrenBelz]

@Brian_S ,

Yes - that is the idea

[Brian_S]

Thank you so much!  I will try this later this morning/afternoon.

[Brian_S]

Sir, I tried the above formula, and I am not getting an error in the name area, but the Workstream Supported block is not populating.  Do I need to put something in the default on the Workstream Supported field?  I know with the others I out something like this "varProject.Value" (I put this in the project field).  I tried this formula in the Workstream Supported default field, but nothing.  Also, thank you so much for your help - much appreciated.

[WarrenBelz]

@Brian_S ,

Sorry, but I do not understand your last post. Does this produce the value you need

LookUp(
   MSL_Master,
   'Name (Title)' = drpName.Selected.Value,
).'Labor Category'

and if so is the Variable now producing the desired result (put varLabor in a Label). If all of this is right, what is the issue now ?

[Brian_S]

Hi sir.  I apologize for the confusion.  I put the below formula in the Name combo box and it is not returning a value for the Workstream Supported text box field, when their is data in this field for a particular person.  I am putting this formula in the Name "OnChange" action.  

Set(varProject,LookUp(MSL_Master,'Name (Title)'=drpName.Selected.Value,Project)); Set(varLabor,LookUp(MSL_Master,'Name (Title)'=drpName.Selected.Value,'Labor Category')); Set(varWork, Coalesce(LookUp(MSL_Master,'Name (Title)'=drpName.Selected.Value,'Workstream Supported'), Blank()));
Set(varFTE,LookUp(MSL_Master,'Name (Title)'=drpName.Selected.Value,'FTE Status'))

[WarrenBelz]

HI @Brian_S ,

You can save a bit of code like this

With(
   {
      _Master:
      LookUp(
         MSL_Master,
         'Name (Title)' = drpName.Selected.Value
      )
   },
   Set(
      varProject,
      _Master.Project
   );
   Set(
      varLabor,
      _Master.'Labor Category'
   );
   Set(
      varWork, 
      Coalesce(
         _Master.'Workstream Supported', 
         Blank()
      )
   );
   Set(
      varFTE,
      _Master.'FTE Status'
   )
)

but that is not the issue and I am getting further unclear here. In your Initial post, you said you wanted to return a blank value when there was nothing returned from this formula

Set(
   varLabor,
   LookUp(
      MSL_Master,
      'Name (Title)' = drpName.Selected.Value,
      'Labor Category'
   )
);

and now you are saying it is not returning anything when there is a value selected ? Is the Default of the text box varLabor ?

[Brian_S]

Hi sir. The field I am trying to populate, if their is data is the Workstream Supported field. The formula I provided in the original post was a formula I used in the Labor Category field and that worked.  If there is no data in the workstream supported field I want it to return a blank value but if there is data I want that to autopopulate. In the workstream supported field.  The default property I had it set to varWork.value in the workstream supported field but it does not autopopulate the field even if there is data in the field. 

I am deeply sorry for the confusing information.  Brian