How do I compute a value (Rate) by first checking if a (Date) falls under 2 lists of dates (ListA) and (ListB) and (Day(s)) of the week, then perform a calculation that will multiply an (Amount) with a (Multiplier)? Amount is a field in a parent table and Rate and Date is in a child table.
Inputs for ListA are:
Saturday & Sunday
I tried making several case statements placing the dates, days and multipliers one by one but couldn't seem to make it work. I made another table that contains ListA, ListB, Days and Multiplier but I'm quite lost as to how to link them to (Rate) and make the correct calculation. Same with the thought of putting the values in a value list.
Any help is greatly appreciated.