Common Department in 1 List


Hi Community,

I would like to ask a question in my new setup where I can flag similar departments in a list. Using only the 3 letters of the code present on the list. In my example below there is 2 Office of the President 1 which is the child of the TMC Main parent and the other is the child of TMC SL.

Requirement: I need to flag both Department using only the 3 letters of the code as their difference. It will flag bother Office of the President and CEO but only the one under TMC main will have a true on the Boolean. I will do this all through out the list. Whether there is an Office of the president on a new entity TMCI or TMC Iloilo. Then these departments will then be imported to a new list (not clear yet why – but for the sake of requirement). I only use as an example the office of the president there will be other departments like laboratory and pharmacy that will be common to all Entity Hospital on the list. I re-created this on a sample model first before I transfer to the actual model. The Only difference between TMC Main and TMC SL is that departments in TMC SL has an extension of _SL at the end. Same with other entities. Also if you can include the next part where I can compile the common departments to be imported to a new list. I would greatly appreciate it.

Thank you in advance,




  • Hello @Jsdeloria21 ,

    Ok, let's go one step at a time.

    My understanding is that your requirement is creating a flat list where all the departments in different hospitals can be aggregated together (i.e., the data for all "Office of the President and CEO" should be aggregated in a single entry).

    If the above is correct, the next question is - do you currently have a flat list of departments or do you need to create it?

    If you do not have it, It does not really matter which item you are flagging (I would recommend using ISFIRSTOCCURRENCE) as long of the code is the same (for the purpose of creating the list).

    • The next step once you create the booleans with the ISFIRSTOCCURRENCE is to import the name and code to a new list (i.e., Flat Departments).

    If you do already have the flat list, you should create a find item driven by the code and both (Office of the President and CEO) will be identified as an item within the new list.

    Using that line item for SUMs would allow you to aggregate the data using SUM.

    Hope the above makes sense.

    Kind regards,

    Bruno Rodriguez

  • hi Community,

    Just to add in case it will help this is the view of the List I am using