Migrate MS Access Online

To begin building your forms and reports, go to the DataPages object on the main left-hand panel and click the New DataPage link on top. This will launch the DataPage wizard.

DATAPAGE 1: Create a New Bug

Start with a simple submission form that allows end users to open a new bug. It will be a recreation of the Access form below:

When you log in as the end user, you should have the ability to create a bug and assign it to somebody else.

• Wizard Step 1 – DataPage Type

  

  Here you can choose the type of app you want to develop: you can build forms, reports to output data, calendars, charts for metrics and HTML DataPages.

  Click on Forms and select Submission Form, then click Next to continue.

• Wizard Step 2 – DataPage Data Source

  
  Under DataPage Properties, there are four fields you can configure:

  1. Select data source – Select the table where you want to store data from the submitted form. In this case, select your Bugs table from the dropdown menu.
  2. Enter DataPage name – Name your submission form, Create a New Bug.
  3. Select Style – Choose from the list of styles for aesthetics. But if you want to fully customize your own look and feel, you can go to the Styles object in Caspio’s main left-hand panel. There, you’ll have the flexibility to edit using actual CSS or Caspio’s wiziwig system. For now, use the default selection.
  4. Select Localization – This dropdown will allow you to select different regional settings that will affect elements like the language of your form. If you wish to configure your own localization, go to the Localizations object in Caspio’s main left-hand panel. For now, use the default selection.

  Under Access and Security, make the following modifications:

  1. Enable Restrict Access.
  2. Make sure that the first radio button to Require Authentication is selected.
  3. Select Users Login in the dropdown. This is how you apply your authentication.

  Click Next to continue.

• Wizard Step 3 – Select Fields

  
  On this screen, select the following from your Available Fields and transfer them to your Selected Fields. These fields will be used in your web form:

  1. AssignedTo
  2. Comments
  3. Priority
  4. Category
  5. Project
  6. Status
  7. OpenedBy
  8. OpenedDate
  9. DueDate
  10. Keywords
  11. Attachment
  12. Summary
  Note that you can select one field at a time by clicking “>”, or move all Available Fields to the right at the same time by clicking “>>”.

  Click Next to continue.

• Wizard Step 4 – Configure Fields

  

  On this screen, you can select each field on the left-hand side and make modifications on the right. You can also rearrange these fields any way you want.

  Modify the fields below under DataPage Elements:

  1. Summary – Push this field to the very top by clicking the up-arrow button at the bottom of the DataPage Elements panel.
  2. Comments – Push this field up underneath the Summary field, then make the following changes in the Standard tab to the right:
     • Under General Options, in the dropdown for Form Element, select Text Area.
  3. Category – Make the following changes in the Standard tab:
     • Under General Options, in the dropdown for Form Element, select Dropdown.
     • Under Field Options, in the dropdown for Source, select Both to use custom values and items from a lookup table.
     • In the Custom Values tab, input Select on the Display field.
     • Delete the mirrored input in the Value field.
     • In the Lookup Table tab, in the Table or view dropdown, select categories_lookup.
     • Make sure that the Field for display and Field for value dropdowns have Category selected.
  4. Project – Push this field up underneath the Category
  5. Priority – Make the following changes in the Standard tab:
     • Under General Options, in the dropdown for Form Element, select Dropdown.
     • Under Field Options, in the dropdown for Source, select Both.
     • In the Custom Values tab, input Select on the Display field.
     • Delete the mirrored input in the Value field.
     • In the Lookup Table tab, in the Table or view dropdown, select priorities_lookup.
     • In the Field for display and Field for value dropdowns, select Priorities.
  6. Status – Make the following changes in the Standard tab to flag each submitted bug as new:
     • Under General Options, in the dropdown for Form Element, select Hidden.
     • Under Field Options, in the dropdown for On load, receive, select Default Value.
     • In the text box to the right, input New.
  7. OpenedBy – Back in the Access form, it is possible to select who opened the bug. It would be easier, though, to use someone’s login credentials instead. Follow the steps below to automatically record the ID of the user who logged in:
     • Under General Options, in the dropdown for Form Element, select Hidden.
     • Under Field Options, in the dropdown for On load, receive, select Authentication Fields.
     • In the dropdown to the right, select ID.
  8. OpenedDate – Automatically stamp the date and time of a submission:
     • Under General Options, in the dropdown for Form Element, select TimeStamp.
  9. Attachments – This field should have File as its Form Element. But since its data type back in the table isn’t File — remove the field by clicking the “X” button at the bottom, then click Yes in the confirmation window that follows.
     Note that you can also go back to your table and change the field’s data type to File. That way you can have attachments on your submission form.
  10. AssignedTo – Make the following changes in the Standard tab to specify who will handle the bug:
      • Under General Options, in the dropdown for Form Element, select Dropdown.
      • Under Field Options, in the dropdown for Source, select Both.
      • In the Custom Values tab, type in Select on the Display field.
      • Delete the mirrored input in the Value field.
      • Under the Lookup Table tab, in the Table or view dropdown, select Users.
      • In the Field for display dropdown, select Username.
      • In the Field for value dropdown, select ID.
  When you look inside the bugs table later on, you won’t see any username. The value stamped instead is the user ID. This is how you link many bugs to a single user, forming a one-to-many relationship.

  Once you’re done configuring all your fields, click the Preview button to see what your form looks like. Use John Doe’s credentials (Username: johndoe | Password: test) when logging in.
  

  Keep in mind that this is a very simple form. In future builds, you can add more customizations like multiple columns, headings, logos, custom buttons and more.

  If you’re happy with the way the form looks, close the preview screen and click Finish back in the DataPage wizard. Now you have your very first submission form.

DATAPAGE 2: Filter New Bugs Report

Below is the live example of your next DataPage:

It’s going to be a report that filters out all the bugs flagged as New. It will also display, using record level security, the bugs that belong to the user who logged in.

Back inside the DataPages object, click the New DataPage link on top to get started.

• Wizard Step 1 – DataPage Type

  Click on Reports, select Tabular, then click Next to move on to the next screen.

• Wizard Step 2 – DataPage Data Source

  Configure the following fields under DataPage Properties and leave the rest as is:

  1. Select data source – Select the Bugs table.
  2. Enter DataPage name – Name this DataPage, Filter New Bugs Report.

  Under Access and Security, make the following changes:

  1. Check Restrict Access.
  2. Make sure that the first radio button to Require Authentication is selected.
  3. Select Users Login in the dropdown.

  Click Next to continue.

• Wizard Step 3 – Search Type

  

  This screen gives you the ability to search data or filter records right away. For this report, make the following changes:

  1. Under Data Selection, select Filter data based on your pre-defined criteria.
  2. Under Record Level Security, check the box to Restrict record access based on user identity.
  3. In the dropdown for Identity field in Authentication, make sure that ID is selected.
  4. In the dropdown for Matching field in current data source, make sure that AssignedTo is selected.

  Now when you log in, you’ll only see data that belongs to your ID. Click Next to continue.

• Wizard Step 4 – Select Filtering Fields

  

  Select Status from your list of Available Fields, then click “>” to move it to your Selected Fields. Now your data will be filtered based on the Status field.

  Click Next to continue.

• Wizard Step 5 – Configure Filtering Fields

  

  To filter out records flagged as New, go under General Options and input New in the Value field.

  Click Next to continue.

• Wizard Step 6 – Select Results Page Fields

  In this screen, you’ll be selecting the fields you want to display on your results page. Choose the following from your Available Fields and click “>” to transfer them to your Selected Fields:

  1. Comments
  2. Priority
  3. Category
  4. Status
  5. OpenedDate
  6. DueDate
  7. Summary

  Click Next to continue.

• Wizard Step 7 – Select Results Page Fields

  

  This screen enables users to edit data via the results page. To emulate the bulk edit function in the live example, go under Edit Records and check the box that says, Bulk edit (applies same updates across multiple records).

  Click Next to continue.

• Wizard Step 8 – Configure Results Page Fields

  
  In order to truncate long text descriptions in your results page, make the following changes:

  1. Under DataPage Elements, select the Comments field.
  2. In the Advanced tab, under Layout Options, check the box to Truncate.
  3. In the textbox to the right, input 20.
  4. Make sure that the dropdown beside it has Characters (nearest word) selected.
  5. Check the box below to Allow users to expand/collapse.

  Click Next to continue.

• Wizard Step 9 – Configure Fields for Bulk Edit

  

  Notice how all the same fields in the results page are included by default in the bulk edit screen. To be able to bulk edit using only the Status field, remove all the other fields one by one with the “X” button. Simply click Yes when prompted to confirm the change.

  When you’re done, implement the following steps to select other statuses:

  1. Under DataPage Elements, select Status.
  2. Under General Options, in the dropdown for Form Element, select Dropdown.
  3. Under Field Options, for your Source, select Lookup table or view.
  4. Under the Lookup Table tab below, for your Table or view, select status_lookup.
  5. For both Field for display and Field for value dropdowns, make sure that Status is selected.

  Click Next to continue.

• Wizard Step 10 – Results Page Options

  Keep the default settings that include displaying 25 records per page. Click Next to continue.

• Wizard Step 11 – Details Page

  

  On this screen, you can enable the ability to drill into details. But select the second radio button to have No Details Page. There is no need to see additional information for now.
  You can now click the Preview button to see what your report looks like. You’ll find that no records are found after logging in as John Doe (Username: johndoe | Password: test). That’s because no data is assigned to him at the moment.

  But before submitting data through your form to test your report, click Finish to save your work.

Click the Preview link on your Create a New Bug form and enter the following dummy data:

• In the Summary field, input SOME TEXT.
• In the Comments field, input SOME MORE TEXT.
• In the Category dropdown, select Application.
• In the Priority dropdown, select Critical.
• In the AssignedTo dropdown, select johndoe.
• Click Submit.
  

When you’re done, close the page and click the Preview link on Filter New Bugs Report.

Now when John logs in, he will see the bug that was assigned to him. On the flip side, a bug assigned to Sarah Smith won’t be displayed since you’re using record level security.

DATAPAGE 3: Filter In Progress Report

This next report is very similar to DATAPAGE 2. The only difference is it’s going to show all the bugs that are currently In Progress.

You don’t have to build a new DataPage from scratch. All you need to do is follow the steps below to duplicate DATAPAGE 2:

1. Hover your cursor over Filter New Bugs Report.
2. Click More.
3. Click Duplicate.
4. Name your new DataPage, Filter In Progress Report.
5. Click Save.

Since it’s the exact same DataPage from before, you’ll need to make one minor change. Hover your cursor over Filter In Progress Report and click Edit to begin.

Click Next until you get to the wizard screen below:

• Wizard Step 5 – Configure Filtering Fields

  Under General Options, in the Value field, replace New with In Progress. Now this report will only filter out bugs that are flagged as In Progress.

  When you’re done, click Finish to save your changes.

If you hit the Preview button on Filter In Progress Report, you’ll once again see that no records are found. This is due to John not having any bugs assigned to him that are In Progress.

Follow the steps below to edit John’s sample data:

1. Click the Preview button on Filter New Bugs Report.
2. Log in as John Doe (Username: johndoe | Password: test).
3. Select the only bug previously assigned to him by clicking the checkbox on the left.
4. Click the Edit link on top to open a popup window for editing records.
   
5. In the dropdown for Status, select In Progress.
6. Click the Update

You’ll now find that Filter New Bugs Report no longer shows the sample bug. But if you go back and click Preview on Filter In Progress Report, that bug will now be displayed.

DATAPAGE 4: Filter In Progress Report

The fourth DataPage you will build is one that has a search interface. Here is the live example for reference:

This DataPage will allow you to pull up all the bugs based on multiple parameters. And similar to DATAPAGE 3, all you need to do is make a copy of an existing DataPage:

1. Hover your cursor over Filter New Bugs Report.
2. Click More.
3. Click Duplicate.
4. Name your new DataPage, Manage Bugs Report.
5. Click Save.

Next, hover your cursor over Manage Bugs Report and click Edit, then click Next until you get to the wizard screens below:

• Wizard Step 3 – Search Type

  Make the following changes:

  1. Under Data Selection, select Allow users to select data using a search form.
  2. Underneath, select Display search form and results on separate screens.
  3. Under Record Level Security, disable Restrict record access based on user identity.

  Your end users can now search and see all the bugs assigned to everyone else.

• Wizard Step 4 – Select Search Fields

  

  You can include as many fields as you want to your search interface, but in the interest if time, choose only the following from your Available Fields. Click “>” to move them to your Selected Fields:

  1. Status
  2. Summary

  Click Next to configure both of your search fields.

• Wizard Step 5 – Configure Search Fields

  

  Make the following changes under DataPage Elements:

  1. Summary – Make the following changes in the Standard tab:
     • Push this field to the top by clicking the up-arrow button at the bottom.
     • Under General Options, in the dropdown for Comparison Type, select Contains.

     Now when a user searches for a letter or phrase, it’s going to display all the bugs that contained that letter or phrase. This method of searching is much easier than using the Equal comparison type, which requires you to input exact values to make a successful search.

  2. Status – Make the following changes in the Standard tab:
     • Under General Options, in the dropdown for Form Element, select Dropdown.
     • Under Field Options, in the dropdown for Source, select Both.
     • In the Custom Values tab, type in Search Any on the Display field.
     • Delete the mirrored input on the Value field.
     • In the Lookup Table tab, in the Table or view dropdown, select status_lookup.
     • Make sure that the Field for display and Field for value dropdowns have Status selected.

  Click Next to continue.

• Wizard Step 6 – Select Results Page Fields

  From your list of Available Fields, select AssignedTo and click “>” to include it to your Selected Fields.
  You can now group your data based on who the bug is assigned to, which is possible since you’re no longer using record level security.

  Click Next to continue.

• Wizard Step 7 – Results Page Editing Options

  Make the following changes:

  1. Under Edit Records, unselect Bulk edit (applies same updates across multiple records).
  2. Under Edit Records, select Inline edit (one record at a time).

  Now you’ve emulated the live example where Inline Edit is the only editing option enabled.

  

  Click Next to continue.

• Wizard Step 8 – Results Page Editing Options

  Notice in the live example how data is grouped by AssignedTo user. You can implement that same configuration by applying the changes below:

  1. Under DataPage Elements, select AssignedTo and push it to the very top of the list by clicking the up-arrow button at the bottom.
  2. While the AssignedTo field is selected, under General Options, check the box to Enable data grouping by this field.
  3. In the dropdown for Grouping display underneath, select Collapsible group.
  4. Make sure that the radio button to make the field Expanded by default is selected.

  At this point, click Preview to check your DataPage. The first thing you’ll see will be your search form.

  

  You can search based on the title Summary and pull up all the bugs based on Status. Select Search Any for now and hit Search.

  

  You are now seeing more than one record because the data imported from Access already contained some data. But you might be wondering: Why is the report displaying the ID instead of the username?
  Before implementing the fix, close the preview page and hit Finish to save your work.

Whenever you submit a bug, you’re stamping the user ID on the Bugs table. All you need to do to display their respective usernames is edit the relationship between your Users table and Bugs table.

Follow the steps below to implement this change:

1. Click the Tables object in Caspio’s main left-hand panel.
2. Click Relationships on top of the resulting screen.
   
3. Right-click the connection between the ID field (Users table), and AssignedTo field (Bugs table).
4. Click Edit to open the popup window to Edit Relationship Settings.
   
5. In the dropdown for Display Value, select Username.
6. Check the box underneath to Use display value on DataPages.
7. Click Save to implement your changes.
8. Click OK in the confirmation popup.
9. Click the Save Layout link on top.

Now go back to DataPages and click Preview on Manage Bugs Report. Make another Search and observe the AssignedTo field. You should now see usernames instead of actual IDs.

Close the preview screen when you’re done checking.

DATAPAGE 5: Add New Comment

The next DataPage will be a form to submit comments. Click the New DataPage link to begin.

• Wizard Step 1 – DataPage Type

  Click the Forms tab and select Submission Form.

• Wizard Step 2 – DataPage Data Source

  Configure the following fields under DataPage Properties:

  1. Select data source – Select Comments.
  2. Enter DataPage name – Name this DataPage, Add New Comment.

  Under Access and Security, make the following changes:

  1. Enable the Restrict Access checkbox.
  2. Make sure that the first radio button to Require Authentication is selected.
  3. Make sure that Users Login is selected in the dropdown.

• Wizard Step 3 – Select Fields

  Transfer the following from your list of Available Fields to your Selected Fields:

  1. Bug
  2. User_1
  3. CTimestamp
  4. Comment

• Wizard Step 4 – Configure Fields

  Make the following changes under DataPage Elements:

  1. Bug – Make the following changes in the Standard tab to link the comments to the actual bug:
     • Under General Options, in the dropdown for Form Element, select Dropdown.
     • Under Field Options, in the dropdown for Source, select Both to use custom values and items from a lookup table.
     • In the Custom Values tab, input Select on the Display field.
     • Delete the mirrored input on the Value field.
     • Under the Lookup Table tab, in the Table or view dropdown, select Bugs.
     • In the Field for display dropdown, select Summary.
     • In the Field for value dropdown, select ID.
  2. User_1 – Make the following changes in the Standard tab to stamp the user’s ID upon submission:
     • Under General Options, in the dropdown for Form Element, select Hidden.
     • Under Field Options, in the dropdown for On load, receive, select Authentication Fields.
     • In the dropdown to the right, select ID.
  3. CTimestamp – Change the following in the Standard tab:
     • Under General Options, in the dropdown for Form Element, select Timestamp.
  4. Comment – Change the following in the Standard tab:
     • Under General Options, in the dropdown for Form Element, select Text Area.

  Click the Preview button to see what the form looks like.

  

  Now you have the ability to select a bug, input comments and submit. When you’re done checking your form, click Finish to save your changes.

DATAPAGE 6: Manage Comments

The next DataPage will be a report to track comments. Click the New DataPage link to begin.

• Wizard Step 1 – DataPage Type

  Click the Reports tab and select Tabular.

• Wizard Step 2 – DataPage Data Source

  Configure the following fields under DataPage Properties:

  1. Select data source – Select Comments.
  2. Enter DataPage name – Name this DataPage, Manage Comments.

  Under Access and Security, make the following modifications:

  1. Enable the Restrict Access checkbox.
  2. Make sure that the first radio button to Require Authentication is selected.
  3. Make sure that Users Login is selected in the dropdown.

• Wizard Step 3 – Search Type

  Use the default settings that will Allow users to select data using a search form.

• Wizard Step 4 – Select Search Fields

  Select Bug from your list Available Fields, then click “>” to move it to your Selected Fields.

• Wizard Step 5 – Configure Search Fields

  Make the following changes under DataPage Elements:

  1. Bug – Make the following changes in the Standard tab:
  • • Under General Options, in the dropdown for Form Element, select Dropdown.
    • Under Field Options, in the dropdown for Source, select Both.
    • In the Custom Values tab, type in Search Any on the Display field.
    • Delete the mirrored input on the Value field.
    • Under the Lookup Table tab, in the Table or view dropdown, select Bugs.
    • In the Field for display dropdown, select Summary.
    • In the Field for value dropdown, select ID.

• Wizard Step 6 – Select Results Page Fields

  Transfer the following from your list of Available Fields to your Selected Fields:

  1. User_1
  2. CTimestamp
  3. Comment

• Wizard Step 7 – Results Page Editing Options

  Use the default settings and skip to the next page.

• Wizard Step 8 – Configure Search Fields

  Use the default settings and skip to the next page.

• Wizard Step 9 – Select Results Page Fields

  Transfer the following from your list of Available Fields to your Selected Fields:

  1. User_1
  2. CTimestamp
  3. Comment

• Wizard Step 10 – Results Page Editing Options

  Use the default settings and skip to the next page.

• Wizard Step 11 – Results Page Options

  Use the default settings and skip to the next page.

• Wizard Step 12 – Details Page

  Select the second radio button to have No Details Page.

Click Finish to save your changes, then click the Preview link on your Manage Comments DataPage. The first thing you’ll see will be a search form.

What you have here is a dropdown list of all the bugs assigned to a user. And once you click Search on your selected bug, you’ll see a report displaying all the related comments and the users who wrote them.

Similar to the changes you’ve made in DATAPAGE 4 regarding table relationships, you can replace the user ID displayed here with its corresponding username:

1. Click the Tables object in Caspio’s main left-hand panel.
2. Click Relationships on top of the resulting screen.
3. Right-click the connection between the ID field (Users table), and User_1 field (Comments table).
4. Click Edit to open the popup window to Edit Relationship Settings.
5. In the dropdown for Display Value, select Username.
6. Check the box underneath to Use display value on DataPages.
7. Click Save to implement your changes.
8. Click OK in the confirmation popup.
9. Click the Save Layout link on top.

Now go back to DataPages and click Preview on Manage Comments. Make another Search and observe the User field. You should now see the username instead of the actual ID.

Close the preview screen when you’re done checking.

In the last two DataPages, you’ll learn how to quickly put together simple charts for your app.

DATAPAGE 7: Total Bugs by Status

For this DataPage, you’ll be building a pie chart that shows the distribution of all the bugs by status. Click the New DataPage link to begin.

• Wizard Step 1 – DataPage Type

  

  Click the Charts tab, find the Pie Charts section, then select Pie.

• Wizard Step 2 – DataPage Data Source

  Configure the following fields under DataPage Properties:

  1. Select data source – Select Bugs.
  2. Enter DataPage name – Name this DataPage, Total Bugs by Status.

  Under Access and Security, make the following modifications:

  1. Enable the Restrict Access checkbox.
  2. Make sure that the first radio button to Require Authentication is selected.
  3. Make sure that Users Login is selected in the dropdown.

• Wizard Step 3 – Search Type

  Under Data Selection, select the second radio button to Filter data based on your pre-defined criteria.

• Wizard Step 4 – Select Filtering Fields

  Use the default settings and skip to the next page.

• Wizard Step 5 – Configure Chart Options

  

  Make the following changes under Chart Elements:

  1. Chart Settings – Make the follwing changes under General Options:
     • In the Title field, input Total Bugs.
     • In the Subtitle field, input by Status.
  2. Category – Under General Options, in the dropdown for Category field, select Status.
  3. Value – Make the following changes:
     • Under General Options, in the dropdown for Value field, select Summary.
     • Make sure that the dropdown for Aggregation has Count selected.
     • Under Display Options, uncheck the box to Show value as percentage.

  Click the Preview button when you’re done to see what the chart looks like. You should now see all eight bugs that are flagged as New, and one that’s In Progress.

Close the preview screen and click Finish to save your work.

DATAPAGE 8: Total Bugs by Month

For your last DataPage, you’ll be building a line chart that shows all the bugs per month. Click the New DataPage link to begin.

• Wizard Step 1 – DataPage Type

  Click the Charts tab, find the Line and Step Charts section, then select Line.

• Wizard Step 2 – DataPage Data Source

  Configure the following fields under DataPage Properties:

  1. Select data source – Select Bugs.
  2. Enter DataPage name – Name this DataPage, Total Bugs by Month.

  Under Access and Security, make the following modifications:

  1. Enable the Restrict Access checkbox.
  2. Make sure that the first radio button to Require Authentication is selected.
  3. Make sure that Users Login is selected in the dropdown.

• Wizard Step 3 – Search Type

  Under Data Selection, select the second radio button to Filter data based on your pre-defined criteria.

• Wizard Step 4 – Select Filtering Fields

  Use the default settings and skip to the next page.

• Wizard Step 5 – Configure Chart Options

  

  Make the following changes under Chart Elements:

  1. Chart Settings – Make the follwing changes under General Options:
     • In the Title field, input Total Bugs.
     • In the Subtitle field, input by Month.
  2. Data Source Settings – Select the second radio button to Use a separate field for each series.
  3. Category (X Axis) – Make the follwing changes under General Options:
     • In the Axis title field, input Month.
     • In the dropdown for Category field, select OpenedDate.
     • In the dropdown for Group date by, select Date rollup.
     • In the dropdown for Date rollup, select Month.
  4. Value (Y Axis) – Under General Options, in the Axis title field, input Total bugs.
  5. Value 1 – Make the follwing changes under General Options:
     • In the dropdown for Value field, select Summary.
     • Make sure that the dropdown for Aggregation has Count selected.
     • Browse and select the Diamond marker.

  Click the Preview button when you’re done to see what the chart looks like. You should now see the number of bugs opened per month.

Close the preview screen and click Finish to save your work.