Skip to main content

How can you adapt the index match function in a transaction list to handle a many-to-many relationship, where a combination of 'Region' and 'Department' might correspond to multiple 'Project Codes' and vice versa?
For example, both Region '1' and Department 'A' could be linked to multiple Project Codes, and a single Project Code could be applicable to various combinations of Regions and Departments. What techniques or formulas can we use to index match on these two properties simultaneously while managing such a many-to-many relationship?

Hi,

I made a simple model reproduction with what I think can help you.

 

1. In my Project codes dimension, I created multiple Region+Department manual mappings:

  • Region1 + Department1
  • Region2 + Department2
  • Region3 + Department3
  • (there can be as many as you want)

This allowed me to have 3 different combinations for one Project code:

 

Then in my transaction list, I just map my line with one Region and one Department.

 

​​​​​​​2. How to map all project codes with your transaction list lines?

Automatically, I calculate how many project codes can be associated to this Region+Department combination. (see Properties: Project codes list, Project codes 1, 2, 3, 4...)

 

For the example, I went up to 4 combinations. But you can do more if needed.

 

3. Render all the mapping results using a text list.

So the property Project codes list formula is looking for when the Region+Department matches one of the Project codes Region+Department mapping:

if(

('ListF Many2Many mappings'.Region = 'Project codes'.Region1

and 'ListF Many2Many mappings'.Department = 'Project codes'.Department1)

or 

('ListF Many2Many mappings'.Region = 'Project codes'.'Region 2'

and 'ListF Many2Many mappings'.Department = 'Project codes'.Department2)

or 

('ListF Many2Many mappings'.Region = 'Project codes'.Region3

and 'ListF Many2Many mappings'.Department = 'Project codes'.Department3)

,

/*result if true*/ 'Project codes'.Name

,

/*result if false*/ blank

)sremove textlist: 'Project codes']

 

4. Then I used this text list to identify separately all combinations.

NOTE: For this, I assumed all my project codes names are using the same format (same characters length)

So the properties are doing mappings with all Project codes listed by using the item + mid functions to transform the text list into dimension items.

  • Project codes 1 = item(Mid('ListF Many2Many mappings'.'Project codes list',1,2),'Project codes')
  • Project codes 2 = item(Mid('ListF Many2Many mappings'.'Project codes list',5,2),'Project codes')
  • Project codes 3 = item(Mid('ListF Many2Many mappings'.'Project codes list',9,2),'Project codes')
  • Project codes 4 = item(Mid(ListF Many2Many mappings'.'Project codes list',13,2),'Project codes')

As a result, I now have all my transaction list rows mapped with all possible project codes.

 

5. Now I can play with the data.

As an example, I created an Amount spreaded on Projects codes by Region+Department:

 

Which I can break down by Region+Department

 

 

You just need to consider all project codes dimensions properties:

'ListF Many2Many mappings'.Amount Lby: 'ListF Many2Many mappings'.'Project codes 1','ListF Many2Many mappings'.Region, 'ListF Many2Many mappings'.Department]

+

'ListF Many2Many mappings'.Amount Lby: 'ListF Many2Many mappings'.'Project codes 2','ListF Many2Many mappings'.Region, 'ListF Many2Many mappings'.Department]

+

'ListF Many2Many mappings'.Amount Lby: 'ListF Many2Many mappings'.'Project codes 3','ListF Many2Many mappings'.Region, 'ListF Many2Many mappings'.Department]

+

'ListF Many2Many mappings'.Amount Lby: 'ListF Many2Many mappings'.'Project codes 4','ListF Many2Many mappings'.Region, 'ListF Many2Many mappings'.Department]

 

 

A final observation is this model would not be really performant as it’s operating formulas on text properties, if your list is big, the performance can be impacted.

 

Hope this helps.

Best,

 

Reply


Ask your question here!

Terms & ConditionsCookie settings