Double entry table

[Jrmy-3622]

Hello everyone.

I have something which is dead simple in Excel and I am wondering how to design that in AWS honeycode.

I have a "double entry" table where in the row-direction I have an employee category level and in the column-direction I have a business line. So something like :

+---------------------+-----------+------------+-----+
| Level/Business line | Marketing | Accounting | IT  |
+---------------------+-----------+------------+-----+
|                   1 |        10 |         20 |  30 |
|                   2 |        15 |         30 |  40 |
|                   3 |        25 |         35 |  50 |
|                   4 |        50 |        100 | 150 |
+---------------------+-----------+------------+-----+

Basically, depending on the business line and the level, the cost of an employee is set. So imagine that in another table I would have something like :

+---------+-----------------------+-------------------------------+----------------------------------+
| Project | Project manager level | Project manager business line |       Project manager cost       |
+---------+-----------------------+-------------------------------+----------------------------------+
| Bla     |                     4 | IT                            | =FORMULA(SEARCH_IN_DOUBLE_ENTRY) |
+---------+-----------------------+-------------------------------+----------------------------------+

Where the FORMULA would go into the double entry table with my parameters from "Project manager level" and "Project manager business line" and return me 150 in this particular case.

How can I achieve that ?

Thank you !

[Alyssa]

Hi @Jrmy-3622, welcome to the community!

I'm happy to show you how to set up a data model for this. The key idea is to have separate tables for the different types of data, and then relate the data in a separate join table.

For your example, you'll want to have separate tables for the Levels and Business Lines. You can then relate them in a join table (that has the cost for each level and business line) by using rowlinks. This is how it looks:

Here are two separate tables, a Levels table and a BusinessLines table:

And here is a Cost table, which has columns that are rowlinks to the tables above and the corresponding costs:

Now in your Project table, you can call the cost column of the row that matches the level and business line. This is an example formula, which uses AND to match both the level and the business line:

=FINDROW(BusinessCost,"BusinessCost[Level]=[Level] AND BusinessCost[Line]=[Line]")[Cost]

I hope this helps! Let us know if you have any other questions.