I'm building a Canvas app with 2 Dataverse tables of data:
Debtor:
| Id | Name | Debtor_Rating | Debtor_Score |
| A1 | Alex | High | |
| A2 | Billy | High | |
| A3 | Chris | Low | |
RatingRefData
| Rating | Score |
| High | 99 |
| Med | 66 |
| Low | 33 |
I'd like Debtor.Debtor_Score to be populated with RatingRefData.Score by doing the equivalent of an Excel VLOOKUP using Debtor.Debtor_Rating.
I can only get it working if the data is populated after I've created the relationship, not before it.
Steps taken to create the relationship:
- Create Many-to-one relationship from Debtor to RatingRefData, name the new lookup column Debtor_Rating
- Create a calculated column in Debtor, called Debtor_Score
- Manually fill in the Debtor_Rating with High / Med / Low
- Debtor_Score is populated with 99 / 66 / 33
How do I get Debtor_Score populated where the Debtor_Rating is already pre-filled? (i.e., All fields in Debtor are migrated from a CRM to this Dataverse table overnight, and I can't seem to import data into the Debtor_Rating lookup column).
