Compute Properties in PreprocessQuery RRS feed

  • Question

  • Hello!!

    I have a table called Units, wich is related to another table called Instalments. Each Unit can have many Instalments.

    Each Unit is assigned an Index and a Basic Instalment amount. On the other hand, each Instalment has its own Index. The Instalment final amount is calculated considering the Unit Index, the Basic Instalment amount and the Instalment Index. This way : Final Instalment Amount=Basic Instalment * Instalment Index /Unit Index

    The instalments table is related to another table called DetailReceipt.

    I want to create a query where are selected only those Instalments wich are not completly paid. This way when I´m creating a new Receipt, I only can choose those Instalments that are unpaid.

    In preprocess query I wrote

    query = From i In query
                       Let FinalInstalmentAmount = i.Units.BasicInstalment * i.Index / i.Units.Index
                       Let Collections = i.DetailReceiptCollection.Sum(Function(a).Amount)

                      Where (FinalInstalmentAmount - Collections) > 0

                      Select i

    But when i run the app no instalment is displayed


    Monday, May 14, 2012 11:16 PM


All replies

  • Hi Belu

    I am not master in linq therefore i can't able to rectify your code. But i can give you another solution to achieve this.

    1. Create a Boolean Property in your Installments entity lets called it IsInstallmetPaid. Make it require.

    2. After that go to your Create New receipt screen. Select CreateNewReceipt_Saving method from write code drop-down. and put something related to below code.

    if (this.DetailReceipt.SelectedItem.Installments.FinalInstallmentAmount == 0)
                    this.DetailReceipt.SelectedItem.Installments.IsInstallmentPaid = true;
                    this.DetailReceipt.SelectedItem.Installments.IsInstallmentPaid = false;

    3. Now Create a Query against your Installment entity lets called it InstallmetPaid. Design your query using query designer. Please follow the below image. In my case its PaidInvoice you can change this as per your requirement. Its just a query the True and False depends on your requirement.

    4. After all this call this query into your Create New Receipt screen using Add Data Item... Bind this query into your Installment look up or Modal Window Picker List. Now run your application and check the result.

    Hope this will help you...

    Rashmi Ranjan Panigrahi

    Tuesday, May 15, 2012 3:17 AM
  • Hi Belu,

    I believe your query will produce the wrong results only when an Instalment doesn't have any DetailReceipts.  This is because Sum returns null over an empty collection in SQL thus it will not meet the conditions specified in the Where clause.  To fix this you need to use the ternary operator to default the Collections variable to 0 when there are no DetailReceipts.

    query = From i In query
            Let FinalInstalmentAmount = i.Unit.BasicInstalment * i.Index / i.Unit.Index
            Let Collections = If(i.DetailReceiptCollection.Any, i.DetailReceiptCollection.Sum(Function(a) a.Amount), 0)
            Where (FinalInstalmentAmount - Collections) > 0
            Select i

    Tuesday, May 15, 2012 8:54 PM
  • Hi snomis!

    Thanks!, but it still doesn´t work.

    It appears a cross in the dropdown list.


    Tuesday, May 15, 2012 9:47 PM
  • Hi Belu,

    Have you enabled break when exceptions are thrown to find out more information on what the underlying issue is?  This is a good starting point to diagnose issues like this.

    Tuesday, May 15, 2012 9:55 PM