posts - 88 , comments - 3 , trackbacks - 0

Creating Master-Detail Screens in LightSwitch

LightSwitch provides support for using relationships between tables in a data source. However, QuickBooks does not natively expose relationships that can be used in LightSwitch. This article will walk you through the steps to set up a LightSwitch master-detail screen using simple queries.

Connect to QuickBooks

If you are connecting to QuickBooks Desktop edition, use the included Remote Connector application. You can use the Remote Connector to connect to local and remote instances of QuickBooks. The Remote Connector must be installed and running on the host QuickBooks machine before beginning this tutorial. See the help documentation for guides to setting up the Remote Connector.

If you are connecting to QuickBooks Online, you will need to obtain the OAuth authentication values. See the "Getting Started" chapter of the help documentation for a guide.

Configure the QuickBooks ADO.NET Data Source

Follow the steps below to use the Data Source Configuration Wizard to configure connection properties and import QuickBooks tables.

  1. In a new LightSwitch project, click "Attach to data source" to add a new QuickBooks data source.
  2. In the resulting wizard, select the Database data source type and in the Choose Data Source step, select CData QuickBooks Data Source.
  3. Enter the required connection properties.

    To connect to QuickBooks Desktop, enter the User, Password, and the URL to the Remote Connector.

    To connect to QuickBooks Online, enter the CompanyId and the OAuth values.

    See the help documentation for more information.

  4. Select two tables that have a master-detail relationship. This demo uses Invoices and InvoiceLineItems but any transaction and line items tables will work.

Databind QuickBooks Data to a Master-Detail Screen

Follow the steps below to use the designer to create the master-detail screen and provide QuickBooks data to it. In this example, you will make a simple screen that displays the Invoices grid on top with the line items grid, Get Invoice Line Items, on the bottom.

  1. In Solution Explorer, right-click the InvoiceLineItems table under Data Sources and click Add Query.
  2. Add a parameter of type String to serve as a placeholder for the InvoiceId field.
  3. Add a filter condition to this query. In this example, you will search for all line items with a certain invoice Id. The parameter will serve as a placeholder for this value.
  4. In Solution Explorer, right-click the Screens folder and click Add Screen. In the resulting wizard, select the Editable Grid screen as the template. In the Screen Data list, select the Invoices table.
  5. In the designer for the new screen, click the Add Data Item. In the Add Data Item dialog, select the query item and then select the query you created in step 3.
  6. In the list of data items, scroll to the bottom of the query until you see the Query Parameters node. Open the properties for the parameter you defined in step 3 and enter "Invoices.SelectedItem.Id" in the Parameter Binding box. This will bind the parameter to the Id of the currently selected invoice on the screen.
  7. Drag the query from the data item list onto the Screen Content Tree. However, you can use layout items to customize the look of the screen however you need.
Now all you need to do is run the application and the grids should be populated with the data from Invoices. Every time you select an invoice, the grid will be populated with the line items for that invoice.


If an error occurs when trying to load the data from these tables, you may see a red "X" where the data should appear. To troubleshoot the issue and see a more descriptive error message, follow these steps:

  1. Open your project properties and change the Application Type to Web.
  2. In Solution Explorer, select your project node and change from Logical View to File View.
  3. Expand the Server node and open the Web.config. Change the Mircosoft.LightSwitch.Trace.Enabled key to "true".
  4. Run your application again to the point that you receive the error. Change the URL to http://URL:port number/trace.axd
  5. Check the trace for requests that have a status code other than 200. Errors with a 500 status code can correspond to an error coming from the QuickBooks ADO.NET driver.

Print | posted on Monday, February 3, 2014 5:44 AM | Filed Under [ .net lightswitch quickbooks ]


No comments posted yet.
Post A Comment

Powered by: