How to capture corrections to salary data sheet

1

In our scenario, we have the salary data coming from HQ. However, the information might be outdated. So, the countries could send us corrections for some employees as they have more latest information - In an Excel workbook, we have a sheet with data from HQ and another sheet with the list of corrections. The column names are mostly the same. Using PowerQuery, I do a Left Anti Join to figure out the records that are only in HQ data sheet and then append the corrections. This helps to keep track of these 2 information separately. Then in the PowerBI dashboard, we show the data from the combined table which has salary data from HQ along with corrections. But for audit trail, we could also show both the original and corrections in a new different view - I am trying to replicate these 2 requirements in Grist.
Here is my thought process:

  1. The original HQ file is imported as a table - let us call it Salary Info
  2. Based on Access Rules, the country finance team would be able to see the salary information of employee in their country. If they want to flag a correction, they can edit the record directly - but then we lose the information on what came from HQ and what was the correction.
  3. Or the finance team could add another row with the key columns like Employee ID and then provide a correction and flag that row as a correction using a toggle column. In this case, I want the corrected data to be used in all the calculations but both the original HQ data and corrections to be viewable on some page, to help us to review the corrections provided.
  4. We might get an updated version of the salary data from HQ - in this case, we will import again and update records using the key column which will overwrite the original data - here, we don’t really need an audit trail, but it would be nice if we can have the old information from HQ versioned with the update date (or something entered by the user) and the new import added as the latest version. I guess that I could do this manually - but then I am not sure how I can ensure that the consumers of the salary data should only see the latest version from HQ and not earlier versions.

Any thoughts on how I should go about implementing this?

2

I’m not sure about the other points, but as for 2., that sounds like a typical widget linking scenario. In other words: Don’t overwrite the original records but put corrections into a separate table that gets linked to the original. If that sounds about right, then you might want to:

  1. Import the HQ data into a ‘SalaryInfo’ table just like you suggested.
  2. For the corrections, create a table ‘SalaryCorrections’ which includes a ref column to ‘SalaryInfo’ (in addition to any other columns you need, like ‘salary_corrected’, for example).
  3. Create a page showing ‘SalaryInfo’. Add another table widget to it that shows ‘SalaryCorrections’ and which gets selected by ‘SalaryInfo’.

Resulting workflow: Select a row in ‘SalaryInfo’. Create a row in the linked ‘SalaryCorrections’ and enter the corrected salary amount there. The row’s ref column will automatically populate to point to the original record from ‘SalaryInfo’. This way, you always have a nice, clean overview of what the original was vs. what corrections were made. You can even add more rows to the ‘SalaryCorrections’ table to indicate multiple corrections to the original ‘SalaryInfo’ record.

Hope this helps!

1 Like
3

Thanks a lot Tom for your suggestion. I have created a separate Salary Corrections table as you have suggested. But my problem is not about widget linking - But my problem is making sure that the consumers of the salary information use the correction if there is one or use the original one. The additional challenge I have is that I have 28 columns which capture different breakup of the salary - hence all these 28 columns have to be available in both the tables. Any thoughts on how I can create some table that captures the latest salary info for each employee ID?

4

Oh, I see what the problem is now. In that case, having two tables may not be the optimal solution because, as you indicated, both would have to maintain all 28 columns (and if this schema should change in the future, both would have to be updated accordingly). That leaves you with two options:

  • The “record model”: Have it all in one table. Add a column that makes use of UUID() to assign each salary entry its own unique id. Then if there’s a correction, just add a new record to the table but re-use the original record’s UUID. That way, we can see both records are linked. Add in another column with a trigger formula NOW() so that we can see when each record was created. Finally, you’d have a formula - whether in this same table or in some other place doesn’t matter - that gets a series of associated (by their UUID) salary records and uses their creation times to find out which one is the latest, then finally outputs that one’s salary info.
  • The “ref list model”: Have the actual data structure all in one table, but just the corrected amounts in a separate one that gets referenced. For each of the 28 columns you mentioned, add a complementary reference list column, name it <the original column name>_corrections. Add a second table, ‘Corrections’, with just one column, ‘amount’. Have all of the 28 reference list columns we’ve just created point to it. Now, whenever there’s a correction to be filed, we can just create a record in the ‘Corrections’ table, enter the corrected amount, and finally link that record from the ..._corrections column that the corrected amount applies to. Finally, we’ll have a formula somewhere that, for each of 28 ..._corrections columns, returns just the latest entry that was added (or, if there are no entries, the original column’s value instead). This way, we get the most up-to-date value for each of the 28.

Let me know if you need a hand with the formulas. Best of luck! :slight_smile:

Edited for clarity… hopefully :slight_smile:

1 Like
5

Tom, thank you so much for the detail. I understand the record model and I would be going ahead with that approach. let me see whether I hit any roadblock. We already have a unique ID there in the form of employee ID from the HR system. So, it would work.

I tried to understand the ref list model by reading it multiple times, but still could not understand fully :). Thank you once again for your time. I would get back if I need any help with formulas.

6

Hey @Siraj_Samsudeen, I took the liberty of throwing together a quick example. This builds on the aforementioned “ref list model” but is much simpler, I really should have thought of this in the first place.
Essentially, it allows you to file corrections to the different salary components per employee, and the main table will only ever show the most up-to-date amounts entered. Maybe you can adapt this to your use case. Take a look: showcase - Grist