top of page

Moji5 Knowledgebase

Displaying data from multiple forms in a view

This tutorial assists you on how to use the M5 platform to create a view that displays data from two different forms.


1.1 Displaying data from multiple forms


Setting up the sample objects

To run this example, you must first create the sample form. Please follow the steps below to do so.

  1. Login to the M5 portal

  2. Create a new application. In your home application, click Create a new form...

  3. Name the form Test Form M5. Leave it the default for the rest


  4. Click Create Form

  5. Click Form Design & Layout


  6. In this form, create a singleline text fields (named Test Field A)


  7. Click Create field


  8. Repeat step 6 and 7 to create another text field (named Test Field B). Your form should like this:


  9. In the same form, create a table named Test Table


  10. Click Create field


  11. In this table, click Edit Detail Form to create two fields (Test Subfield A and Test Subfield B)


  12. You should now have the following:


  13. Now publish this form by clicking on Publish and return at the bottom of the page


  14. Click Generate now to generate a views for this form


  15. Go to your app home then run this application. Click Run


  16. Key a record into the form, and specify two rows inside Test Table. You should now have the screen shown in the following screenshot. Save the record.



Inspecting the default generated view


When you publish the form above, it will generate a default view. Navigate to the application home and choose to edit the default Test Form M5 view. Click Test Form M5 view


If you choose to edit the Datasource for the view, you will be presented with the screen shown in the following screenshot.




If you run the application and open the view, it will only show 2 columns (the two columns from your parent form), as shown below:



What you want to do now is to display the subform columns (Test Subfield A and Test Subfield B) in this same view. We will explore this in the next section.


Modifying the view to include 2 additional columns


What you've created so far above is a form and a table within a form. This translates in M5 to two forms - the parent "Test Form M5" and the child "Test Table" form. The

data is stored in two separate tables. The screenshot below illustrates the relationship between these two generated tables:



Follow the steps below to add the two additional columns to your view.

  1. Click to edit the datasource of the view

  2. Choose Define fields by SQL. Write your sql then click Retrieve columns

    E.g SQL:

SELECT * FROM (
SELECT [QF_Test_Form].[ID],[QF_Test_Form].[Test Field A], [QF_Test_Form].[Test Field B],[SF_Test Table].[Test Subfield A] AS [Test subfield A],[SF_Test Table].[Test subfield B] AS [Test subfield B], ROW_NUMBER() OVER ( ORDER BY [QF_Test_Form].[ID] ASC) AS 'SqlRowNum' 
FROM [QF_Test_Form]
INNER JOIN [SF_Test Table] ON [SF_Test Table].[ParentID] = [QF_Test_Form].[ID]) AS x 
WHERE SqlRowNum>=1 AND SqlRowNum<=20 ORDER BY [ID] ASC

3. You should now have the screen shown below.


4. Save the changes to the view.


These few steps actually to creating an INNER JOIN between the two tables. You may notice at this point that you can actually run the view, and that it'll work.


Run your view, you should be able to see all the parent rows matched with the child table rows, as shown in the Figure below.



Comments


bottom of page