Answered

Powerapps Dataverse equivalent of Excel VLOOKUP for non date content

(0) Share

Report

Posted on by Solsus1

Hi,

Another "VLOOKUP" query from me. I have previously had some help with a calculation between tables where the information concerned dates (Solved: Re: PowerApps / Dataverse equivalent of Excel VLOO... - Power Platform Community (microsoft.com))

I now need to do the same, but for data that is not related to dates (i.e. not an ADDDAYS formula).

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 one column according to matched data in another column. I need to do this to support a more complicated lookup and calculation involving a third table that sets out project utilisation time according to the team role of the person on the project.

In simple terms, my set up is as follows:

________________________________________________________________

Table 1 (Team_Roles) ¦

_________________________________________________________________________¦

Column1 (Team_Member_Name) *Primary* ¦ Column2 (Team_Role) ¦ Column3 (Team_Memb_Mob) ¦

FreeText LOOKUP to T3/C1

Chris Brown ¦ Director ¦ 07777 777777 ¦

Douglas Black ¦ Manager ¦ 07777 777778 ¦

Lesley White ¦ Assistant ¦ 07777 777779 ¦

__________________________________________________________________________________________________________

________________________________________________________________________

Table 2 (Contract_Details)

_______________________________________________________

Col1 ¦ Col2 ¦ Col3 ¦ Col4 _______________ ¦ Col5 ¦

(Project_Ref) *Primary* ¦ (Team_Member_Name) ¦ (Team_Role) ¦ (Project_Utilisation_Rate) ¦(Compl_Lev) ¦

FreeText LOOKUP to T1/C1 Calc Calc LOOKUP to T4/C1

CR0001 ¦ Chris Brown ¦ ? (i.e. Director) ¦ ? (i.e. 0.15) ¦ High ¦

CR0002 ¦ Chris Brown ¦ ? ¦ ? ¦ Low ¦

CR0003 ¦ Douglas Black ¦ ? ¦ ? ¦ Medium ¦

CR0004 ¦ Lesley White ¦ ? ¦ ? ¦ High ¦

CR0005 ¦ Lesley White ¦ ? ¦ ? ¦ Low ¦

CR0006 ¦ Chris Brown ¦ ? ¦ ? ¦ High ¦

CR0007 ¦ Douglas Black ¦ ? ¦ ? ¦ Low ¦

____________________________________________________________________________________________________________________

Table 3 (Project Utilisation)

__________________________________________________________________________ ¦

Column1 (Team_Role) *Primary* ¦ Column2

(Proj_Util_Rate_High) ¦ (Proj_Util_Rate_Med) ¦ (Proj_Util_Rate_Low) ¦

FreeText Numeric

Director ¦ 0.30 ¦ 0.25 ¦ 0.20 ¦

Manager ¦ 0.25 ¦ 0.20 ¦ 0.15 ¦

Assistant ¦ 0.20 ¦ 0.15 ¦ 0.10 ¦

_________________________________________________________________________

Table 4 (Project Complexity)

____________________________________¦

Column1 (Comp_Level) *Primary* ¦

FreeText

High ¦

Medium ¦

Low ¦

____________________________________

Column 2 on table 2 is a lookup against column 1 of table one (a lookup against the primary column (Team_Member_Name).

Easy enough through the creation of a relationship.

However, I want to automatically populate column 3 of table 2 (Team_Role) by doing a "VLOOKUP" of the Team_Member_Name value in each column 2/table 2 row against the Team_Member_Name column 1 of table 1 to return the column 2 of table 1 value.

I then want to automatically populate column 4 of table 2 (Team_Role) by doing a "VLOOKUP" of the Team_Role value in each column 3/table 2 row against the Team_Role column 1 of table 1 to return the column 2 of table 3 value.

Can I do this?

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (4)

Answers (1)

João Santos 323 on at

Like (1)

Report
Copy link

Link copied!

Hey again,

So, slight difference but still possible, if I'm reading it right.
First, is the Role a Table or a Text field? I feel like this should be a Table. Then relationships to wherever that table is needed (the 3 above for example).
And is the Utilisation Rate defined globally for all projects or per project? If Globally defined then you might actually just be able to use Table 3 as the Reference Table and link it to the other two as a relationship.

Second, the Team Role column on Table 2:
Use a Real-time Workflow (instant/synchronous) or a Power Automate Flow (Async) to, on create of a Contract Detail or on update of the Team Member field, copy the Role from Table 1 to Table 2;
This should ensure that anytime you create or update an existing record the related role is copied to the Table 2 record.

Finally, copying the values from the Roles table:
This, unless I'm mistaken, you can just use a Calculated field again on Column 4, the formula should be lookuprolename.project_utilisation_rate
You should be able to access most fields on directly related N:1 relationships.

Hope this helps

Was this reply helpful? Yes No
Solsus1 33 on at

Like (0)

Report
Copy link

Link copied!

Thanks again joaodsantos.
I've update the tables in my original post to clarify my approach.

Role - Table1/Column2 (Team_Role) is lookup to Table3/Column1 (Team_Role) - not free text

Utilisation - Utilisation Rate is defined per project based on a lookup to a project complexity table (reflected above as a lookup from Table2/Column5 (Compl_Lev) to Table4/Column1 (Comp_Lev). Utilisation rates vary according to team role and project complexity level (see additional table 3 colums). I was going to deal with this in Table2 by having three utilisation columns (H/M/L) and creating a total column to add them together.

Note also that Table2/Column2 (Team_Member_Name) is lookup to Table1/Column1 (Team_Member_Name)

As you say, I feel like I should be able to do a lot through calculations. However, I have found an issue.
As a test, I can successfully create a calculation in Table2/Column3 (Team_Role) that pulls text entries from Table1 to Table2 (ie. the Team_Memb_Mob), but it doesn't work for columns that are lookups to other tables, such as Table1/Column2 (Team_Role).

My calculation for the Team_Memb_Mob (that works) is
cr090_team_roles.cr090_team_memb_mob

However, the following does not work
cr090_team_roles.cr090_team_role

If I could resolve this issue, I think I could crack the rest.

I really appreciate the help here and apologise for not being able to work this out by myself. Any direction is welcomed.

Was this reply helpful? Yes No
Verified answer

João Santos 323 on at

Like (1)

Report
Copy link

Link copied!

No need to apologize of course, we're all here to learn a bit more 🙂
Yes, the tricky thing here is getting lookup fields from related entities since those can't be "copied" as calculated fields.
If it was me I'd just use a realtime workflow, especially since we're only copying a single field.
I'm not sure how comfortable you are with Power Platform so I'll leave you with a quick tutorial on how to copy a field using real-time workflows on the classic editor, sorry if you already know all this:
From your solution create a new Workflow:

Choose the following options but on Table choose your Contract Details Table:

Classic workflow designer comes up (real-time WFs use the old Workflow Engine)
Select the following options on the WF config pane:

When you tick the "record fields change" option the Select button becomes available, make sure you select your Team Member Name field so that the WF also triggers on update.

On the steps pane just click Add Step and then Update Record. When the step is added click Set Properties. The Contract Details form will come up and you can then choose the fields you want to update. Select the Role field and on the Right Pane Assistant

choose Look for: "Your Team Roles Table" and on the Field you should be able to select the Role.
Click Add then Ok. Your role field will look something like this:
Hit save and close, then on the main WF form hit Save and then Activate.
(I'm assuming your Team Member field is required and can never be blank after creation, otherwise a condition to clear the role if the team member is also blank would be best practice but I've assumed it's required)

That should be it, if you now create a new contract detail it should pick up the role from the related table, and if you then have the calculated field in place to bring the rate then it will also bring that straightaway.
Seeing your Complexity Level T2C5 there I'm guessing you'll need a couple of Condition to do if Low bring Low Rate, if Medium bring Medium rate, etc. but that's all doable, just add a few conditions on the calculated field

Hope this helps

Was this reply helpful? Yes No
Solsus1 33 on at

Like (0)

Report
Copy link

Link copied!

Hi again joaodsantos,
Apologies for taking a while to confirm. I have been working hard through your guidance to complete the job as much as possible (without asking for more help!), solving a couple of other problems along the way. Your walkthrough of the workflow was invaluable.

I was also successful with the multiple conditions for the different complexity levels in the calculated field.

To note, yes, the Team Member field is required and can never be blank after creation, but I've noted your point on good practice to clear fields if logical to do so.

Thanks again, for the time you've put in to help a newbie!

Was this reply helpful? Yes No