Update column from another table

[CuriousMind]

I have two tables in the Model Driven App. The primary table is called Service Task where the second table is called Activity Record. In the Service Task table is a Job Number column where the user inputs a job number (BC-12345) when in the app.

The Activity Table is set as sub-grid in the Service Task form. I would like to see the Job Number column appear in this Activity Table form, populated. How can I link these two? 

A many-to-one relationship has already been formed.

[Stevieed]

Go to the view editing page of your Activity Table subgrid view, add a new column, choose related, Service Task, select job number as a new column for each Activity

[rfainter123]

If you want to show the Job Number in the Activity sub-grid view only, then the solution that @Stevieed suggested will work.

If you want the Job Number to be on the form of the Activity, then there are a couple of ways to do this:

1. You can create a Quick View from the Service Task table and show it on the Activity table. This will be a read only view. You could show in the sub-grid via the method mentioned above, and also show it on the table using the Quick View. 
2. You can also stamp the Activity with the Job Number whenever the Activity is created or the Related lookup is changed using a native Dataverse workflow. That way Job Number would be a column on the Activity table that is reportable, and then you could just show it in the sub-grid view. If this method is appealing, I'd be happy to walk you through it.

[CuriousMind]

Thank you @rfainter123 . I can see the column Job Number within the Activity Record View as explained through by @Stevieed . I would like to see it within the Form and really welcome the walk through as offered by you @rfainter123 !

[rfainter123 · Accepted solution]

Hi @CuriousMind,

Absolutely! There are two easy ways to reach your objective, one being a read-only view of the job number, and the other would actually add the job number as a column onto your activity record. 

READ-ONLY METHOD:

1. From make.powerapps.com, find the Service Task table and open Forms.
2. You can choose to modify the out-of-the-box Quick View, or create a new one:
   
3. Configure the Quick View form to include the Job Number, then save and publish.
4. Open the form you want to use for the Activity table and insert the view into the form from the Components menu. It will ask you for the relationship, which is to Service Task, then it will ask for the form you just created/modified:
   
5. Save and publish.

COPYING JOB NUMBER TO ACTIVITY:

The second method would copy the Job Number into a column on the Activity record. This is useful if you need to easily report all activities with a specific job number, or something to that tune:

1. Create a column on the Activity table that is the same data type as the Job Number on the Service Task table.
2. Add the new Job Number column to the Activity form. Save and publish.
3. Create a new Dataverse process/workflow (you can do this from within the Default Solution😞
   
   
   
4. Give it a name, select your Activity table, and uncheck the Run workflow in the background checkbox. Do not select a template.
5. In the editor window, you may want to change Scope to Organization. Keep the selection for Record is Created, then add Record fields change, and select the Regarding column:
   
6. Next, click Add Step to update the record:
   
7. Make sure the Update: selection is set to your Activity table, then click Set Properties.
8. In the form editor window, click inside the Job Number field that you added in step 2. Then, in the Look for: dropdown, under Related Entities, select Regarding (Service Task). Then in the dropdown below Look For: select Job Number and click Add. It will populate in the window below that. Select Job Number and click OK below that. You will see the highlighted Job Number copied into the Job Number field on the Activity form. Click Save and Close:
   
9.  From the workflow editor window, click Save, then click Activate:
   
10. Now go back into the Activity form and make the Job Number column read-only so that users cannot change it. Save and publish.

This method will add the Job Number to the Activity record every time a new record is created or when the Regarding lookup is changed.

If one of these options works for you, please mark it as a solution!

[CuriousMind]

Thank you @rfainter123 ! Worked perfectly!