PowerApps / Dataverse equivalent of Excel VLOOKUP

[Solsus1]

Hi,

I'm a newbie, so apologies if this is really a simple one. I have been looking through the documentation for hours without any luck.

I am creating a PowerApp based on a set of tables I am creating in Dataverse. I have two tables and I want to create the equivalent of an Excel VLOOKUP calculation between two tables. I have created a relationship between the two relevant tables but can't seem to work out the calculation required to draw in information. In simple terms, my set up is as follows:

________________________________________________________________     

Table 1 (CR_Complexity)                                                           ¦      

________________________________________________________________ ¦  

Column1 (CR_Complexity) ¦ Column2 (CR_Complexity_Days)  ¦    

    HIGH                              ¦                  84                                ¦

    MEDIUM                        ¦                  42                                ¦

    LOW                               ¦                  21                                ¦

________________________________________________________________

________________________________________________________________     

Table 2 (CR_Change)

 _______________________________________________________

Col1                   ¦ Col2                     ¦ Col3                              ¦ Col4             ¦ Col5                                                            ¦

(CR_Reference)  ¦ (CR_Complexity)  ¦ (CR_Complexity_Days)  ¦ (CR_Start)     ¦ (CR_Target Date)                                        ¦

CR0001              ¦          HIGH          ¦                   ?                  ¦     30/12/22  ¦  [(CR_Start_Date)+(CR_Complexity_Days)] ¦

CR0002              ¦          HIGH          ¦                   ?                  ¦     30/12/22  ¦  [(CR_Start_Date)+(CR_Complexity_Days)] ¦

CR0003              ¦          MEDIUM    ¦                   ?                  ¦     30/12/22  ¦  [(CR_Start_Date)+(CR_Complexity_Days)] ¦

CR0004              ¦          LOW           ¦                   ?                  ¦     30/12/22  ¦  [(CR_Start_Date)+(CR_Complexity_Days)] ¦

CR0005              ¦          HIGH          ¦                   ?                  ¦     30/12/22  ¦  [(CR_Start_Date)+(CR_Complexity_Days)] ¦

CR0006              ¦          MEDIUM    ¦                   ?                  ¦     30/12/22  ¦  [(CR_Start_Date)+(CR_Complexity_Days)] ¦

CR0007              ¦          LOW           ¦                   ?                  ¦     30/12/22  ¦  [(CR_Start_Date)+(CR_Complexity_Days)] ¦

____________________________________________________________________________________________________________________________________________

What I want to do is as follows:

For column 3 of each record in Table2 (CR_Complexity_Days), pull in the relevant CR_Complexity_Days value from Table1 by matching the CR_Complexity value (High/Medium or Low) in table2 column2 (CR_Complexity) against the CR_Complexity column values in Table1. Then I want to add the relevant CR_Complexity_Days value to the CR_Start date (column 4) to get the CR_Target_Date (column 5). Column 5 being a calculated field (column3 + column4).

Simple stuff in Excel, but PowerApps/Dataverse has me stumped!

[AhmedSalih]

Hello, @Solsus1, are you building a PowerApps Canvas or Model-Driven?

 

If my reply helped you, please give a 👍 , & if it solved your issue, please 👍 & Accept it as the Solution to help other community members find it more.

I am primarily available on weekdays from 6-10 PM CT and 5-10 PM CT on weekends.

Visit my Blog: www.powerplatformplace.com

[Solsus1]

Hi Ahmed,

My thinking was to create a portal app, but I have so far been testing out the canvas app functionality. I wan't to keep calcualtons in the database if possible, in order to make sure the user experience is not laggy.

[joaodsantos]

Hi,

This could be done in a few different ways but at the core of it I don't think you need a lot of configuration, especially if there's no calculation involved, just displaying the number of days for each complexity.

On your Changes table your column 2 should be a N:1 relationship to the Complexity table. I don't think you need a Column 3 on Changes, just pull the related value from the Complexity table.

Depending on where you want to present this, if this is for a Portal, just create a System view that brings the related table (Complexity) column for Days.

If using FetchXml it's the same, just reference the column from the related table.

Accessing related table data from CanvasApps is also possible, see here

If you want to duplicate the data from Table 1 to Table 2 into a new Column you can use a workflow to copy the value from the related table. I don't think that would be necessary but don't know your specific requirements.

Hope this helps

[Solsus1]

Hi joaodsantos,

Thanks for the steer. I can see how doing the work in the app would work and will try that if I can't manage it in the tables. I actually oversimplified my original post, as I do plan to have a calculated field CR_Start_Date)+(CR_Complexity_Days). I have updated my original post to clarify what I'm doing. Is it possible?

[joaodsantos · Accepted solution]

Ah, that makes sense. But again, should be able to achieve it all on the Dataverse side.

Try a calculated field on the Change table with a formula with something like ADDDAYS(CR_Complexity.days, date_to_count_from)

That'll create a column on the change table.

Something to keep in mind. This calculated column is calculated on Retrieve, so if you change the complexity days in the future it'll reflect it for all Change records.

[Solsus1]

Hi joaodsantos,

Cracked it! Thanks for your help. I did exactly as you said. My problem was simply about getting the formula correct.

For the benefit of any other newbies watching, I did the following:

Step 1: From the table view screen, select the relationship option to create a relationship between the two tables (on the next screen select the +New Relationship > Many to One button).

Step 2: Create the Many to One relationship between the two tables. In my case, the table containing the list of days for each level of complexity is the CR_Complexity table (I didn't make any changes to the General and Advanced section).

Step 3: Go back to the table that you want to make the calculation in and create a new column for the calculation column, being sure to select the 'Calculated' option in the Behaviour section. Once saved, you can select the Edit option to add a calculation, which brings up a second screen.

Step 4: Add your calculation to pull the information from the other table on the basis of what I would call an 'Excel Vlookup' approach. In my case the formula was as follows:

o No Condition used (I suppose I could stipulate that the CR_Complexity field has to be populated, but it seems to make no difference. If the data is not there for the calculation, the field remains blank).

o Action formula for me is:

AddDays([table pulling data from].[column pulling from on parent table], [field pulling start date from on this table])

AddDays(cr5c6_cr_complexity.cr5c6_cr_complexity_days, cr5c6_cr_request_date)

Result: For each record in my table I set a request date and a level of complexity. Depending on the level of complexity chosen, the target date (i.e. target completion date for task) is automatically set by adding the number of days it should take for the specified level of complexity to the start date.

[ywp]

(Apologies, I know this thread is old)

Did you have to manually fill in the CR_Complexity field for CR_Target_Date to be populated?

[Solsus1]

Hi ywp,

The CR_Complexity field is a dropdown selection one with options of LOW/MEDIUM/HIGH. In the app, a user would select a category for a particular task and the relevant CR_Target_Date would be drawn in and added to the task start date to populate a target completion date field.

Hope this helps.