In Designer mode


Let us adjust the Services document in a similar manner, giving users the option to specify a property set for each material.

  1. In Designer, open the editor of the Services document configuration object.
  2. On the Data tab, add the PropertySet attribute with the CatalogRef.MaterialOptions type to the document tabular section.
  3. Fill the Choice parameters links property for this attribute by adding the MaterialsAndServices.MaterialOrService attribute from the list of available attributes to the list of parameters.

    By doing this, you specify that the list of MaterialOptions catalog items available for selection in the PropertySet field contains only items subordinate to the material selected in the MaterialOrService column.
  4. Drag this attribute from the form attributes pane to the form controls pane, to the tabular section, after the MaterialOrService item.
  5. In the editor of the Services document configuration object, click the Other tab and open the object module.
  6. Open the Posting event handler procedure and, in the section that generates register records, assign a value to the PropertySet dimension of the BalanceOfMaterials register as shown in listing 15.4.

    Listing 15.4. Posting() procedure (fragment)

    // register BalanceOfMaterials Expense
    Record.Material = SelectionDetailRecords.MaterialOrService;
    Record.PropertySet = SelectionDetailRecords.PropertySet;
    Record.Warehouse = Warehouse;
  7. Since during the previous lesson you adjusted the document posting procedure to get all the document data using a query, update the query to get the new document attribute (listing 15.5).

    Listing 15.5. Posting() procedure (fragment)

    Query = New Query;
    // Specifying the temporary tables manager used by the query
    Query.TempTablesManager = TTManager;
    Query.Text = "SELECT
        |    ServicesMaterialsAndServices.MaterialOrService,
        |    ServicesMaterialsAndServices.MaterialOrService.MaterialServiceType AS MaterialServiceType,
        |    ServicesMaterialsAndServices.PropertySet,
        |    SUM(ServicesMaterialsAndServices.Quantity) AS QuantityInDocument,
        |    SUM(ServicesMaterialsAndServices.Total) AS TotalInDocument
        |INTO DocumentMaterialsAndServices
        |    Document.Services.MaterialsAndServices AS ServicesMaterialsAndServices
        |    ServicesMaterialsAndServices.Ref = &Ref
        |GROUP BY        
        |    ServicesMaterialsAndServices.MaterialOrService,        
        |    ServicesMaterialsAndServices.MaterialOrService.MaterialServiceType,
        |    ServicesMaterialsAndServices.PropertySet";
    Query2 = New Query;
    Query2.TempTablesManager = TTManager;
    Query2.Text = "SELECT
        |    DocumentMaterialsAndServices.MaterialOrService,
        |    DocumentMaterialsAndServices.MaterialServiceType,
        |    DocumentMaterialsAndServices.PropertySet,
        |    DocumentMaterialsAndServices.QuantityInDocument,
        |    DocumentMaterialsAndServices.TotalInDocument,
        |    ISNULL(CostOfMaterialsBalance.CostBalance, 0) AS Cost,
        |    ISNULL(BalanceOfMaterialsBalance.QuantityBalance, 0) AS Quantity
        |    DocumentMaterialsAndServices AS DocumentMaterialsAndServices 
    You also have to adjust the last query that performs the check for negative balances during real-time posting. Instead of getting general balances for materials specified in the document tabular section, it should get the balances for materials with the property sets specified in the document rows (listing 15.6).

    Listing 15.6. Check for negative balances during real-time posting

    Query3.Text = "SELECT
        |    BalanceOfMaterialsBalance.Material,
        |    BalanceOfMaterialsBalance.PropertySet, 
        |    BalanceOfMaterialsBalance.QuantityBalance
        |    AccumulationRegister.BalanceOfMaterials.Balance( , (Material, PropertySet) IN (
        |        SELECT
        |            DocumentMaterialsAndServices.MaterialOrService,
        |            DocumentMaterialsAndServices.PropertySet 
        |        FROM
        |            DocumentMaterialsAndServices)
        |        AND Warehouse = &Warehouse) AS BalanceOfMaterialsBalance
        |    BalanceOfMaterialsBalance.QuantityBalance < 0";
    Query3.SetParameter("Warehouse", Warehouse);
    QueryResult = Query3.Execute();
    SelectionDetailRecords = QueryResult.Select();
    While SelectionDetailRecords.Next() Do
        Message = New UserMessage();
        Message.Text = String(- SelectionDetailRecords.QuantityBalance) + " units shortage for """
            + SelectionDetailRecords.Material + """ with """ + SelectionDetailRecords.PropertySet 
            + """ property set.";
        Cancel = True;

Add comment