ITEM formula for on flat list


Hi, I have two modules (see pictures). The Attainment Employee Position Check module checks which positions an employee has during the current planning year and the Attainment Employee Active Position Check module will use the information used in the previous module. So in the example below an employee named TBH has the position Channel Manager 3 - Australia starting 2020-jan-01 and ending the postion 2020-feb-29. Based on this logic the Boolean in the other module should be TRUE for Jan and Feb, for Channel Manager 3 - Australia.  I am trying to build some logic [item('FL-Position') = First Position AND Start>= Fist Position Start Date AND End() <= First Position End Date), but, since both the employee list and position list used in these modules are flat lists i cannot use ITEM. Does anyone know a workaround this issue? 


Kind regards







Best Answer

  • nathan_rudman
    Answer ✓
    this error message means you have a typo.
    There is no list called "FL-Position" in your model. Type the correct name and it will work.


  • Hi
    Item works on any kind of lists, numbered, flat, hierarchical, time, line item subset, anything. Your formula should be working, there must be another problem: format is not the same, typo... I cannot tell from these screenshots

    More importantly, unless you have a very specific reason to do your architecture this way, I'd say it is profoundly unsound. How do you load the position the positions into that table ? What happens if one employee has more positions ? Your formulas are going to be **** because you need to repeat them for each for every sets of line items etc.
    Usually positions are loaded on an Employee by Time module, so you can easily load it, track it and it extends dynamically.

  • @CommunityMember117741 

    I agree with @nathan_rudman that ITEM will work on any list formatted line item. You won't be able to use PARENT though.

    In that case, you'll want to create a system module that uses the flat list and have the hierarchy in that system module.

    The position list with start and end dates can also be pulled into a calculation module that contains the two dimensions: employee and positions.

  • The previous build was in levels, but, due to the number of levels in the hierarchy the build became to complicated for the clients.  An employee can have up to three positions under a given year (not at the same time). This solution will be a little ****, but, easier for the client to understand. 


    Looking at Anapedia it says the list needs to be in a hierarchy.  I get an error message: "FL-Position is not recognized as a hierarchy in the model. The item function should take a hierarchy as argument"



  • Thank you! There was one more "space" i was missing. Now it works.