Summing based on another modules field
Hopefully a straight forward one.
I have 2 lists. Campaign and Show ID
Multiple campaigns can have the same show however they are not within the same hierarchy.
I have a module INP01:Campaign Details that has the Show ID as a line item - not all campaigns have show IDs.
I have another module INP02:Episodes where the user inputs the number of episodes that a show will play in a given month. A show should have the same number of episodes in a month (called Unique Episodes) regardless of the campaign and users sometimes only update one campaign instead of all of them.
I am trying to create a formula to detect when the episodes in the month for any given campaign is not equal to the average for the show - this will detect if a campaign has not been updated.
in my head the formula is something along the lines of IF ISNOTBLANK('INP01: Campaign Details'.Show ID) THEN if Unique Episodes <> Unique Episodes[Sum:'INP01:Campaign Details'.'Show ID']/Unique Episodes[Count:'INP01:Campaign Details'.'Show ID'] then false else true else true
say we had 3 campaigns to a show id and 4 episodes in a month the above formula would be 4 = 12/3 which is correct. If one was changed to 5 you would have (4 or 5 depending on campaign) <> 13/3 which would flag as needing correction.
The sum function does not seem to work. I thought lookup would also work but that is not the case.
Any help would be appreciated.