cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Gjakova
Helper II
Helper II

Creating and Updating two tables (history and current)

Hi there, I'm trying to do the following thing:

I have two tables: TableX and TableX_History

Both tables look exactly the same, but we want to use TableX_History to maintain the history of TableX in SQL, example:

IDRevenue *ContractTypeCityEtc...CreatedByCreatedDateUpdatedByUpdatedDate
15000xxxxxxxxxDonald TrumpJanuary 5th, 2016  
2900xxxxxxxxxGeorge BushJune 7th,  2007Barack ObamaJuly 11th, 2020

So in PowerApps we have a form based on TableX (which in reality consists of 20+ columns). Now let's say that Michael Jordan wants to update the contract that George Bush signed on June 7th, 2007 and was last updated by Barack Obama on July 11th, 2021. I want TableX_History to look like this:

IDRevenue *ContractTypeCityEtc...CreatedByCreatedDateUpdatedByUpdatedDate
2900xxxxxxxxxGeorge BushJune 7th,  2007Barack ObamaJuly 11th, 2020

And TableX to look like this:

IDRevenue *ContractTypeCityEtc...CreatedByCreatedDateUpdatedByUpdatedDate
15000xxxxxxxxxDonald TrumpJanuary 5th, 2016  
249xxxxxxxxxGeorge BushJune 7th,  2007Michael JordanApril 18th, 2021

If next year Lionel Messi updates that same contract, TableX_History should look like this:

IDRevenue *ContractTypeCityEtc...CreatedByCreatedDateUpdatedByUpdatedDate
249xxxxxxxxxGeorge BushJune 7th,  2007Michael JordanApril 18th, 2021
2900xxxxxxxxxGeorge BushJune 7th,  2007Barack ObamaJuly 11th, 2020

And TableX like this:

IDRevenue *ContractTypeCityEtc...CreatedByCreatedDateUpdatedByUpdatedDate
15000xxxxxxxxxDonald TrumpJanuary 5th, 2016  
28000000xxxxxxxxxGeorge BushJune 7th,  2007Lionel MessiApril 18th, 2021

If I understand correctly, I should have an UpdateIf and Patch function, but I don't know how to do a syntax that sends the previous value of a form to my History table and updates the Actual table with the news records, when a user clicks a button (Button: Save).

Could someone help me out? Thanks so much!

1 ACCEPTED SOLUTION

Accepted Solutions
WarrenBelz
Super User III
Super User III

Hi @Gjakova ,

You can use either - Patch would be best for a single record - assuming you have already written to TableX

With(
   {
      wTable:
      Lookup(
         TableX,
         ID=YourTableXID
     )
   },
   Patch(
      TableX_History,
      {ID:wTable.ID},
   {
      Revenue:wTable.Revenue,
      Contype:wTable.ContractType,
      . . and the rest . .  . .
   }
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

View solution in original post

3 REPLIES 3
WarrenBelz
Super User III
Super User III

Hi @Gjakova ,

You can use either - Patch would be best for a single record - assuming you have already written to TableX

With(
   {
      wTable:
      Lookup(
         TableX,
         ID=YourTableXID
     )
   },
   Patch(
      TableX_History,
      {ID:wTable.ID},
   {
      Revenue:wTable.Revenue,
      Contype:wTable.ContractType,
      . . and the rest . .  . .
   }
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

View solution in original post

WarrenBelz
Super User III
Super User III

Hi @Gjakova ,

Just checking if you got the result you were looking for on this thread. Happy to help further if not.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Hey there! Since I am using SQL I made use of System Versioned Tables, so all the work happens in SQL. I would've tried your option, but because I have so many fields I assumed it would take too long (to create and load) and the maintenance.

I think that for forms with less fields your option is a good solution, so therefore I'll accept your solution. Although I haven't tested it yet. For large forms (and if you are using SQL) I would recommend to make use of System Versioned Tables.

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (2,706)