In Lightswitch, how do I make a calculated field/property in an Entity/Table based on data in a record from a separate table?

Question

I currently have a formula in c# for a calculated property in a Lightswitch Entity/Table (HouseIncome).

result = NumberInHouseHold >= 1
? (AnnualHouseHoldIncome / (7880 + (NumberInHouseHold * 4180)))
: 0;

The NumberInhousehold has a data type of Decimal. The AnnualHouseHoldIncome is a data type of Money. The two numbers are variables which are changed each year. The PFPLevel is a calculated property with a Percent data type. For each record in this Table there is also a EmploymentDate property with a Date data type.

This calculation works great for 2017 but in 2018, and subsequent years, I would have to recode the calculated field with the new guidelines and information displayed in the PFPLevel field would no longer be accurate for 2017.

I would like to make the PFPLevel calculation based on data entered in a record of the HouseIncome table and the variables drawn from the FPLGuidlines table according to the Employment date. So, if a person in 2017 had a record with an employment date of 6/17/2017, that record would have the PFPlevel calculated on with NumberInHousehold, AnnualHouseHoldIncome, and Guidelines drawn from 2017 record.

In the FPLGuidelines table there would be four fields; Id, YearID, BaseIncome, PerIndividulalAmount which would have a new record each year. I am not sure what data types I would need for each of these properties/fields.

Answer 1

One option would be to use the following type of entity data types:

Alongside the following type of approach to your calculated property:

partial void PFPLevel_Compute(ref decimal result)
{
    if (EmploymentDate.HasValue)
    {
        var hiy = EmploymentDate.Value.Date.Year;
        var gl = DataWorkspace.ApplicationData.FPLGuidelines.Where(e => e.Year == hiy).Execute().FirstOrDefault();
        if (gl != null)
        {
            result = NumberInHouseHold >= 1
            ? (AnnualHouseHoldIncome / (gl.BaseIncome + (NumberInHouseHold * gl.PerIndividualAmount)))
            : 0;
        }
    }
}

The above example is based on your HouseIncome.EmploymentDate property being a nullable DateTime.