How To: Create a Form where you can enter data in a “header/detail” or “primary/line items” style.

This topic shows you how to create a form where you can enter data in a header/detail or primary/line items style. This scenario is also sometimes called item-line items or parent-children forms. Using a sales order form for example, a single order usually has header information such as customer, order date, tax, and amounts. The order detail usually contains one or more items, each with information such as the product, quantity, and unit price. You may also want to add functionality so that entering information in the detail section of the form, updates fields in the header section.

The key to implementing header-detail forms is to use an item view for the header section, and a list view (editable or read-only) for the details/line items section. Additionally, there must be some common property between the SmartObject that provides the header data and the SmartObject that provides the detail data. Typically, this means that the SmartObject providing the detail data has a property like 'Parent Record ID', which is used to 'bind' or 'join' the detail items to their parent.

Example Header/Detail form

Scenario

As part of a sales order process you want to create one sales order for multiple line items. In this scenario, a sales person enters values like customer ID and order date in the header section, and sales order line items such as product and quantity in the sales order details section, and finally clicks the Create new Order button to save the line items and header information.

Steps

To set up the scenario, you create SmartObjects that store header records, details records, products, and customers. You create an association between the details and products SmartObjects to populate a drop-down list and an association between the details and header SmartObjects to create a relationship between the line items and the header. You design views to act as the header and detail sections on the form. You then create expressions to calculate line item totals and the total amount of the sales order. You also create batch execution rules to link the detail items (children) to the header item (parent).

Prior to working through this how-to, you should know how to work with the category system and add elements such as SmartObjects and views. If you do not know how to build the basic application elements, see: Getting Started (with the Designer).

Setup Steps

The following steps set up the scenario from start to finish. If you want to skip the setup steps and view the topic step, go to Configure Rules to Update the Header and Detail of a Form.

  1. From the Designer, create the SmartObjects listed below. Use the tables as a guide for adding properties.
    Sales Order Header
    This is the SmartObject that acts as the header.
    PropertyData TypeKeyRequiredUnique
    SalesOrderIDAutonumberYesNoYes
    CustomerIDNumberNo

    No

    No

    OrderDateDate/Time

    No

    No

    No

    SubTotalNumber

    No

    No

    No

    TaxAmountNumber

    No

    No

    No

    TotalDueNumber

    No

    No

    No


    Sales Order Details
    This is the SmartObject that acts as the details or line items for a sales order.
    PropertyData TypeKeyRequiredUnique
    SalesOrderDetailIDAutonumberYesNoYes
    ProductIDNumber

    No

    No

    No

    QuantityNumber

    No

    No

    No

    UnitPriceNumber

    No

    No

    No

    LineTotalAmountNumber

    No

    No

    No

    SalesOrderHeaderIDNumber

    No

    No

    No


    Product
    This is the SmartObject that allows you to select a product when entering line items.
    PropertyData TypeKeyRequiredUnique
    ProductIDAutonumberYes

    No

    		Yes
    ProductNameText

    No

    No

    No


    Customer
    This is the SmartObject that stores customer information.
    PropertyData TypeKeyRequiredUnique
    CustomerIDAutonumberYes

    No

    		Yes
    CustomerNameText

    No

    No

    No

    See the How To: Create A SmartBox SmartObject article of the How To: Build a Simple Application series for steps on creating a SmartObject.
  2. Edit the Sales Order Details SmartObject and add the following associations:
    • One-to-many association between the Sales Order Details and Product SmartObjects
    • One-to-many association between the Sales Order Details and Sales Order Header SmartObjects. (This is not necessary to make the header-details form work, but it is useful to make the association so that you understand the relationship between the line items and the header)
    1. To add the first association, right-click the Sales Order Details SmartObject and select Edit.
    2. Select the Associations tab and click Add.
    3. Select the Product SmartObject and click Next.
    4. On the Add Association screen, select the options as shown below to create the one-to-many association. Click Next.
    5. On the Relationships screen, select the ProductID field and click Assign.

    6. On the Assign property screen, select the ProductID property of the Product SmartObject from the drop-down and click OK.
    7. Click Finish on the Relationships screen to finish the one-to-many association between the Sales Order Details and Product SmartObjects.
    8. Click Add to configure the next association.
    9. Select the Sales Order Header SmartObject and click Next.
    10. On the Add Association screen, select the options as shown below to create the one-to-many association. Click Next.
    11. On the Relationships screen, select the SalesOrderHeaderID field and click Assign.
    12. On the Assign property screen, select the SalesOrderID property of the Sales Order Header SmartObject from the drop-down and click OK.
    13. Click Finish on the Relationships screen to finish the one-to-many association between the Sales Order Details and Sales Order Header SmartObjects.
    14. Both associations are listed for the Sales Order Details SmartObject.
  3. In this step, you generate Item views for the Customer, Product, and Sales Order Header SmartObjects. After you generate the Customer and Product views, enter at least two sample records for each view.
    See the How To: Create an Item View and a List View article of the How To: Build a Simple Application series for steps on creating views.
  4. Edit the Sales Order Header Item view and remove the Create, Save, Delete and Load buttons.

    In the next step, you remove the Create, Save, Delete and Load buttons from the Sales Order Header Item view. These buttons are not necessary on this view.

    1. Right-click the Sales Order Header Item view and select Edit.
    2. On the Layout page, select the Create button and press Delete on your keyboard. Select Remove all dependencies and click OK. This automatically removes all dependencies of the deleted item.
    3. Repeat this for the Save, Delete and Load buttons, and then click Finish to save the view.
  5. From the Sales Order Details SmartObject you design an Editable List view and name it Sales Order Details List. Display the ProductID, Quantity, UnitPrice, and LineTotalAmount fields. Enable list editing. Add a sum aggregation for the Line Total Amount column and add an expression to calculate the line item total.
    1. Right-click the Sales Order Details SmartObject and select Design View.
    2. Name the view
      Sales Order Details Editable List
      and confirm the type is set to List. (List views enter or return many records at a time.) Deselect the option Call this method when the form loads if it is selected and then click Create.
    3. On the Layout page click Create Labels and Controls.
      .
    4. On the Create Labels and Controls screen, select the ProductID, Quantity, UnitPrice, and LineTotalAmount fields. Select the Edit Options as shown below. This enables editing of the list. In the Additional section, un-check the Allow the user to manually refresh list option if it is checked. Click OK.
    5. Rename the following controls in the Add/Edit Item row by clicking the control and changing the Name of the control property:
      • Quantity: Edit Row Quantity
      • UnitPrice: Edit Row Unit Price
      • LineTotalAmount: Edit Row Total Amount

    6. Select the Line Total Amount column. In the Properties panel, select the Column tab. Click on the Add link next to Sum to add a sum aggregation for the column.
    7. Select the 0 data label that is added by the Sum aggregation, and then change the Name property to List Items Total Amount.
    8. Select the Edit Row Total Amount text box in the Add/Edit Item row, and then click the ellipsis button next to the Expression property.
    9. You will add a function that will automatically calculate the total for each line item added to the order. On the Select an Expression... screen, click Add.
    10. Name the expression
      Calculate Line Item Total.
      Switch to the Operators tab of the Context Browser, and then expand the Operators node. Drag the Multiply operator into the expression editor, noting when the box changes to blue which means you can drop the operator.
    11. Switch to the Context tab and expand the tree to locate the control Edit Row Unit Price. Drag and drop the Edit Row Unit Price on one side of the multiply operator. Drop the Edit Row Quantity on the other side of the multiply operator.
    12. Click OK to save the expression and OK again to apply the expression.
    13. Click Finish to save the view.

Configure Rules to create the Header and Detail records when the form is submitted

In the following step, you add rules that will create the header and details records when the sales person submits the form.

  1. You design a form and name it Sales Order. You add the Sales Order Header Item and Sales Order Details Editable List views to the form. You add a label and button control and create form rules to add validation and create list item records.
    1. Right-click the Sales Order Header Item view and select Design Form.
    2. Name the form
      Sales Order
      and then click Create.
    3. On the Layout page click Views, then drag the Sales Order Details Editable List view into the drop zone as show below.
      Add View
    4. Click the View Title of the Sales Order Header Item view and then type Order Header.
    5. Do the same for the Sales Order Details Editable List view and name it Order Detail.
    6. Click the Toolbox tab and drag the Label control onto the canvas as shown below. Change the Text property of the control to Sales Order Capture Form.
    7. Add a Button control to the bottom of the form and set its Name property to Create Sales Order and it’s Text property to Create new Order. (You must add this button on the form level, not the view level, because the button will need to call methods on both the Header view and the Details view. Only Form-level rules will have access to call methods for the views in that form; view-level rules do not know of other views on the same form.). Save the form.
    8. On the Rules page, click Add Rule.
    9. Add a rule for the Create Sales Order button that calls the Create method of the Sales Order Header Item view and add a Form passes validation condition to the rule so that the Customer drop-down list is a required field. To do this select the Events tab, click When a control on a Form raises an event in the Form Events section and then select Create Sales Order.
    10. Select the Conditions tab and click the Form passes validation in the Validation section.
    11. Click configure next to the condition and select the Required check box next to the CustomerID Text Box control. Click OK.
    12. Select the Actions tab, click Execute a View method in the SmartObject Interaction section and then select Sales Order Header Item and Create as shown below.
    13. Click the configure link next to the action and assign the Sales Order Header Item view’s controls with the SmartObject’s input properties as shown below. To do this, expand the Sales Order Header Item view in the Context Browser and drag the control values into the SmartObject fields.
    14. Click the Output mappings tab and then drag and drop the SalesOrderID Return Property into the SalesOrderID Text Box, and into the SalesOrderID property of the Sales Order Header SmartObject as shown below. You do this so you can see the ID of the new Sales Order Header record that is created. When you use batch execution for the rule actions, it is not necessary to save the returning ID to a control on the view, but you must save the returned property into the SalesOrderID property of the Sales Order Header SmartObject. You do this because you will use this ID property when creating the detail records - this mapping is what will 'bind' the details records to their parent record.
      Note that when you use Offline forms, the context of the form is not available for batch execution while offline. If you build forms for offline use, you must only map the returned ID (SalesOrderID) to the ID property of the parent SmartObject (SalesOrderID of the Sales Order Header SmartObject), which is bound to the parent view (Sales Order Header Item view).
    15. Click Finish to save the configuration. You have set up the first rule that creates the Sales Order HEADER item. The important part is to save the return value (the sales order header ID) because you use that value in the next rule when you create the individual sales order items, so that you can bind the detail items (children) to the header item (parent).
    16. Add a second action to call the Create method for each item that was added to the Sales Order Detail Editable List view, and configure the rule to execute the actions in a batch. To do this select Execute a View method for items that are in a specific state in the SmartObject Interaction section, select the Sales Order Details Editable List view, select the Create method and select the Added state, and then select the then complete the following in a batch execution type as shown below.
      What you are doing is telling the system to call the Create method for each line item that was added to the details section of the form.
    17. Click configure next to the action and configure the input properties to accept their values from the SmartObject properties (not the controls on the list view). On the Input Mappings screen, do not click the Auto Map button. Instead, use the Context Browser to set up the input mappings from the Sales Order Detail SmartObject. To do this, expand the Sales Order Details Editable List view in the Context Browser, then expand the Sales Order Details SmartObject and map the SmartObject properties (ProductID, Quantity, UnitPrice, and LineTotalAmount) to their corresponding Input Properties, as shown below.
      Note that you must map the SmartObject properties from the context browser on the right, to the SmartObject properties in the Destination section on the left. This may seem counter-intuitive, but this is how you must map values from the editable list so that the system can 'read' the data for each line that was added to the details view.
    18. Set up the SalesOrderHeaderID input property for the details SmartObject to accept its value from the SalesOrderID property of the Sales Order Header SmartObject. To do this, expand the Sales Order Header Item view, expand the Sales Order Header SmartObject and then drag the SalesOrderID property into the SalesOrderHeaderID Input Property of the Sales Order Details Editable List view as shown below. This is the key to making the header/detail scenario work: You use the returned ID of the parent record and pass it into the foreign key of each child record that is bound to the parent. Click Finish to save the configuration and then click OK to save the rule.
    19. You just configured a header/detail form where the rules create the header item first, save the resulting HeaderID, and then call the Create method for each item that was added to the header, passing the header ID into each item’s Create method.
    20. Now you will add some rules to transfer data from the details view to the header view. Strictly speaking this is not necessary to make the primary-line items scenario work, but you may need to write rules that transfer values such as total amount from the details view to the header view.
      Add another rule to copy the value of the List Items Total Amount label to the Sales Order Header Item view's Subtotal Text Box, when the List Items Total Amount control’s value changes. To do this, click Add Rule, select When a control on a View raises an event from the View Method section, and configure the rule to fire when the List Items Total Amount on the Sales Order Details Editable List view is Changed as shown below.
    21. Select the Actions tab, select the Transfer data action from the Data Transfer section, and then click the configure link to set up the action.
    22. You want to copy the sum value into the SubTotal Text Box of the Sales Order Header Item view, so expand the Sales Order Details Editable List view, then expand Controls and Footer Rows, and then drag the List Items Total Amount control into the SubTotal Text Box control of the Sales Order Header Item view. Click OK to save the configuration.
    23. Click OK to save the rule and Finish to save the form.
  2. In this step you add an expression that sets the Total Amount text box value to the sum of the Sub Total and Tax text boxes.
    1. Right-click the Sales Order Header Item view and select Edit.
    2. On the Layout page select the Total Due Text Box and then click the ellipsis next to the Expression property as shown below.
    3. The Add Expression screen opens. Click Add and then name the expression Calculate Total. Select the Context tab of the Context Browser and expand the Sales Order Header Item view, expand Controls, and then drag the SubTotal Text Box control into the drop zone as shown below.
    4. Select the Operators tab, expand the Operators function, and then drag the Plus function into the drop zone as show below.
    5. Select the Context tab again, and then drag the TaxAmount Text Box control into the drop zone as shown below. Click OK to save the expression.
    6. Make sure to select the Calculate Total expression and then click OK to apply the expression to the control.
    7. Click Finish to complete the changes to the view.
  3. Test your form by running the Sales Order form. Verify that the sub total is passed into the header detail row, and then click the Add button to save a new sales order into the K2 Database.
    1. To test the form, select the Sales Order form, then click Run.
    2. In the Order Header, enter a value in the Customer ID text box and select a date from the calendar.
    3. In the Order Detail, click Add new row and select a product. Add a quantity and unit price for the new item and then click Add new row again. Enter details for another list item, and then click Add new row again. Note that the Line Total Amount is updated, and that the Sub Total and Total Due in the Order Header is updated.
    4. Enter an amount for the tax and verify that the Total Due is updated.
    5. Click Create new Order and wait for the operation to complete. Note that the Sales Order ID is updated with a value of the new record in the database.
    6. To confirm that the details items are associated with the parent, use Management to execute the Get List method for the Sales Order Details SmartObject. You should see that the Sales Order Header ID property is set to the same value as the Sales Order ID value that you saw in the previous step.
    7. In Management expand the Categories node, expand the Sales Order node and then select the Sales Order Details SmartObject.
    8. Select the Get List method in the Methods section and click Execute.
    9. On the Execute SmartObject Method screen, click Execute to return all records.
    10. Scroll to the right of the screen, and then note that the SalesOrderHeaderID property is set to the same value as the Sales Order ID value that you created in step above.
Review

There are many uses for header/detail type forms. In this example you used an Item View called Order Header and a List View called Order Detail to create a single input form called Sales Order Capture Form. This form is used to capture a new sales order in a header/detail fashion. This form allows for more than one line item per order to be created at once.