[Model Driven APP] - Record keep the old values from lookup column

[Tea_Programmer]

Hello,

I have entity called HC Reports with the following structure:

No.Col (primary key)	HC Country (text)	HC Function (text)
1233	FRANCE	HUMAN RESOURCES
2343	FRANCE	ENGINEER

And other entity called Report with the following structure:

ReportID	HC Report (lookup to HC Reports)	HC Country (HC Report)	HC Function (HC Report)	Date
1323123	1233	FRANCE	HUMAN RESOURCES	01/08/2023
234234	2343	FRANCE	ENGINEER	01/08/2023

Every month the HC Report entity is updated and some HC Functions could be updated with new values.

The report data is created for each month, but every time a record from HC Reports updates the value, the reference used in Report entity is updated, that's a normal behaviour in DataVerse.

I need the records from Report keep the previous values from the HC Reports lookup column and only the new added records use the new/changed values from the lookup.
Is it possible to achieve this?

Thank you,
Ruben

[gulshankhurana]

Hi @Tea_Programmer 

If you would like to keep the values as originally entered then you'd need to store those values locally in the Report table.

For this, you'd first need to create the fields/columns (with like-for-like field/column types) in the Report table for which you want the old values to be retained. You can set the fields to be read-only if you don't intend to update them in the Report table.

You can then create a Power Automate flow that copy-pastes the values from HC Reports to Report table. You'd need to think of a trigger that'd make the flow run only once. 

I hope this information helps.

Kind regards

Gulshan

-----------------------------------------------

Please give a Thumbs Up to my reply if you appreciate my effort. If it helps resolve your query then please accept my reply as a solution.

[Fubar]

The reference does not actually change, the internal GUID in the Lookup will be the same.  Lookup fields are linked/referenced by the internal GUID of the record - what is displayed to the user is the current value of the Primary Name column on the record with that GUID. - hence if you are changing the value on the lookedup record that is what is displayed to the user.

If you want to save a historic display value, you will need to copy that value to a text field.  If you do this, you just need to be mindful of if there are scenarios where the Lookedup record is updated and you need that change updated in your text field.

[Tea_Programmer]

Hi @Fubar ,

I was thinking of another solution involving Relationship mapping.

In the relationship that joins the two tables using HC Report (lookup to HC Reports). I created two more mappings for HC Country and HC Function.
I followed the example from this site: https://meganvwalker.com/tips-tricks-in-model-driven-power-apps/#bonus_tip_3

In the Reports table I created two more columns exactly the same as HC Country and HC Function, supposedly when I change a lookup value (HC Report) these fields should change since they are connected with the mapping, right? But for some reason this feature does not work.

[Fubar]

You need to remember that relationship mapping does not work for all scenarios.  Generally it is a client side operation and so doesn't apply to server side operations e.g. data import, creating records from Flow, Workflow, or Plugin