locked
Query Calculating Totals in Lightswitch (parameterized) RRS feed

  • Question

  • I am writing a simple payroll program using lightswitch 2013 but I am finding it difficult to calculate the gross pay for respective employees. I could calculate the gross pay for all employees all together but I can't see to be able to calculate for different employees respectively (like calculating the gross pay for the logged on user). can anyone put me through, I have tried to many options but to no avail. Here's is my code below but it does not filter for the currently logged on user.

    Dim amountE = From e In Me.DataWorkspace.ApplicationData.Earnings

                Dim sumE = Aggregate sums In amountE.Execute() Into Sum(sums.Value)

                Me.AmountEarned = sumE.ToString


    Thursday, August 7, 2014 9:12 AM

Answers

  • Try this:

    1) Add the Employee table (or a query based on it) to your screen.

    2) Add a data item to the screen - type Employee.

    3) Drag the Employee data item to the screen to get an autocomplete box and set the "Choices" property to the Employees table or query.

    4) Create a query on the Earnings table with an Integer parameter named EmployeeId and set the filter to Employee.Id = EmployeeId

    5) Add the query to your screen.

    6) Click on the EmployeeId parameter of the query and in the parameter binding, choose Employee.SelectedItem.Id.

    Now, when you select an employee from the Autocomplete box, you will see only the selected Employee's earnings.

    • Marked as answer by Angie Xu Monday, September 1, 2014 7:08 AM
    Sunday, August 10, 2014 4:56 AM

All replies

  • You only want to grab the earnings for the logged in user?  You can use an expression like this to grab all of the earnings for the logged in user:

    C# (sorry)

    this.DataWorkspace.ApplicationData.Earnings.Where(u => u.User == this.Application.User.Fullname)

    This assumes that "Earnings" has a field listing the users Full Name (User), which is the same as the full name given for the user at account creation in the LS app (this.Application.User.Fullname).


    Friday, August 8, 2014 8:33 PM
  • thank you billym99. the above will work on one of my screen. nut i also have another screen which I need to filter the earnings by an employee either selected from an autocomplete box. Can you help with the code for this? I will really appreciate it.

    mjpet007

    Friday, August 8, 2014 8:42 PM
  • Try this:

    1) Add the Employee table (or a query based on it) to your screen.

    2) Add a data item to the screen - type Employee.

    3) Drag the Employee data item to the screen to get an autocomplete box and set the "Choices" property to the Employees table or query.

    4) Create a query on the Earnings table with an Integer parameter named EmployeeId and set the filter to Employee.Id = EmployeeId

    5) Add the query to your screen.

    6) Click on the EmployeeId parameter of the query and in the parameter binding, choose Employee.SelectedItem.Id.

    Now, when you select an employee from the Autocomplete box, you will see only the selected Employee's earnings.

    • Marked as answer by Angie Xu Monday, September 1, 2014 7:08 AM
    Sunday, August 10, 2014 4:56 AM