Patch function for lookup column default

[ashokpershad]

Appreciate if someone could help.

I have created twoTables in Dataverse:

1. First Table named "Company" have different columns like: Engineer Name (text type), Phone number, address etc.
2. Second Table names "Man Hours" have columns like : CEngineer (Lookup from Company table) required column, Date Started (Date Type) required column, Date Ended (Date Type), Remarks (Text Type).

I made Canvas App, where I added one blank gallery and connected its data source to "Man Hours" table. I manually added different controls for each columns like DropDown control for CEngineer, Date Control for Date columns etc. Now I updated these control ITEMS property with respective columns in Table. For example, Dropdown control ITEMS property is 'Man Hours'. CEngineer and DEFAULT property is "1". Also for Date control the DEFAULTDATE property is ThisItem.'Date Started'. 

Now I added one + icon so that once user press this icon it will create new record in gallery for them to enter details.

I need your support, what should be Icon ONSELECT property should be so that everytime I press it, it will create blank row in the gallery for users to update.

Thanks in advance for your support.

[Mira_Ghaly]

@ashokpershad 

reset(formName)

[v-qiaqi-msft]

Hi @ashokpershad,

Do you want to dynamically add a new row for the Gallery once you click the button?

Could you  please share a bit more about the scenario?

I assume that every row within the Gallery has controls to enter details, and you want to click a button to copy a row of controls to create a new record, right?

Set the OnVisible property of the screen of your app to following formula:

ClearCollect(BlankRow,'Man Hours')

Set the Items property of the Gallery control to following formula:

BlankRow

Set the OnSelect property of the Button control to following formula:

Collect(BlankRow,CountRows(BlankRow))

Then you could get a new row with your controls in the Gallery.

Regards,

Qi

[ashokpershad]

Dear @v-qiaqi-msft ,

Thank you for your reply.

Adding blank row works perfectly fine but my question contains one more requirement:

As mentioned, I have two dropdown controls in same data source which are lookup columns from other tables in 'Man Hours' data source. When I am adding this new blank row the way you suggested, these dropdown are not showing any data. Also, appreciate if you could suggest me how to patch these entire controls in the data source with one button. I feel, Patching lookup columns is not as easy as Text columns.

[v-qiaqi-msft]

Hi @ashokpershad,

Do you mean that you have a LookUp field named CEngineerin Man Hours table and you set the Items property of the Dropdown as'Man Hours'. CEngineer?

I want to confirm with you that if the CEngineer (Lookup from Company table) exists in the 'Man Hours', you should set the Items as 'Company'.'Engineer Name'  or Choices('Man Hours'. CEngineer).

That's impossible that you could set a LookUp column Dropdown as'Company'.'Engineer Name' 

I have a test and the situation is confirmed on my side, please take a try as below.

Set the Items property of Dropdown box control to:

Choices('Man Hours'.CEngineer)

Set the Default property of Dropdown box control to:

ThisItem.CEngineer.Value

Set the AllowEmptySelection property of Dropdown box control to:

true

For how to patch these data in the Gallery to the data source, I recommend as below:

ForAll(
  Gallery1.AllItems,
  Patch(
          'Man Hours',
           Defaults('Man Hours'.),
           {
             Column1: Dropdown1.Selected,
             Column2: Dropdown2.Selected,
             Column3: TextInput1.Text,
             ... 
           }
  )
)

Hope it could help.

Regards,

Qi

[ashokpershad]

Dear @v-qiaqi-msft 

I tried doing as you explained in your reply but I am not able to add the DEFAULT property of Dropdown box as you explained. I am getting following error. ".value" property is not available.

ThisItem.SEngineer.value

I added the Add Row "+" Icon and updated all its property as per your advice in your first reply. Now when I Preview app and press this button, its adding the row but the dropdown boxes are empty but it seems there are few things but not visible.

Even the patch function is throwing error:

[v-qiaqi-msft]

Hi @ashokpershad,

Could you please check mu solution one more time?

I refer to the "Value" rather than "value", could you please notice the problems with capital and lower?

In addition, the way how you set the Default property of the Dropdown box depends on how you set the Items property. Please make sure that you have set the Items as below:

Choices('Man Hours'.CEngineer)

Please do attention the details of the formula I provided.

Regards,

Qi

[ashokpershad]

@v-qiaqi-msft 

I think I am doing same and not making any mistake. Please have a look at below screenshots.

ITEMS PROPERTY:

DEFAULT PROPERTY:

When i am taking my cursor on the "Value" , following message is showing:

Name isn't valid. This identifier isn't recognized.

Please note that, I am working on Dataverse and since DROPDOWN control is not available in Dataverse so I copied the control from PowerApps web. This way I am able to get all its property in Dataverse. 

[v-qiaqi-msft · Accepted solution]

Hi @ashokpershad,

My bad that I found that you have a LookUp field in the Dataverse, which differs from the Lookup field in the SharePoint list.

It is an known issue that the Dropdown box could not display the options correctly from time to time. To be much more stable and convenient, I recommend use a Combo Box control instead.

If you insist on the Dropdown box, you should prepare yourself mentally that it is not stable.

Please set the Items of the Dropdown to:

Choices([@'Lulai Trackers'].crba2_TheFood).'Food Name'

Set the Default to:

ThisItem.'The Food'.'Food Name'

Note that the 'The Food' is a Lookup field that looks up from 'Food Name' field in another Table.

On your side, please modify as below:

Choices('Man Hours'.CEngineer).'Engineer Name' // Assume that looks up from the 'Engineer Name' in Company table.

ThisItem.CEngineer.'Engineer Name'

I want to explain to you that all the function that a Dropdown box has could be achieved in Combo Box.

If you replace it with a Combo Box, you just need to set as below.

Items:

Choices('Man Hours'.CEngineer)

DefaultSelectedItems:

ThisItem.CEngineer

Hope it could help.

Apologize again for not note your detailed information.

Regards,

Qi

[ashokpershad]

@v-qiaqi-msft 

It works, at least those errors are not coming. Thank you so much.

Back to my first question of how to patch? Now do Patch also use different formulas for Dropdowns ?

I inserted following formula for patching item into the Man Hours table in Dataverse but it is showing error:

ForAll(glryManHours.AllItems, Patch('Man Hours', Defaults('Man Hours'), {MHCode:tbcode.Text, ESupervisor: ddEsuper.SelectedText, SEngineer:ddSEngr.Selected, 'Date Started': Datestarts.SelectedDate, 'Date Completed':Datecompl.SelectedDate, 'Actual Work Hours':tbManhours.Text, Remarks: tbRemarks.Text}))