Method Community

I'm looking to design the following type of screen:

• A grid showing list of all customers (fullname field in the Customers table)
• Certification attributes for each customer (Certification 1, 2, 3 levels, for example) are also columns in the grid. These would be checkboxes. These will be manually updated in the grid for now
• Filters at the top of the screen for Calendar Year, Quarter and Sales Rep

For further clarification, I'll use a simple example:

• I have a Customers table, with all regular columns/fields
• I have a Certifications table, with 3 Yes/No Certification fields
• I added a dropdown in the Certifications table that shows the Fullname field from the Customers table. I called the field CustomerFullName. This Certifications table should now be linked to the Customers table.

Hi Mike,

You need to first add entries to your table. You can either enable adding and editing on the grid and set the appropriate actions in the After Edit and After Add events. However, the simplest and most time efficient solution would be to add the fields in your table onto the screen, then add a Select link to your grid. That way you'll be able to add/edit entries on the screen without having to add actions in two different places on your grid in order to be able to add and edit entries.

You can add the Certification fields to the Customer tables as linked fields but remember that you will not be able to edit them, as linked fields do not allow modifications. The first approach along with my suggestions above will work for you.

- Ashur

I'm not sure exactly what you mean.

The Certifications table is currently blank and has the following fields:

• Fullname
• Year
• Quarter
• Certification 1
• Certification 2
• Certification 3

Yes, you'll need to add records (entries) to your Certification table. What I meant was you can add the fields which are in your table to your custom screen. That way any data you enter into those fields and save will be inserted into the table. The screenshot below shows the the screen editor; the left side shows the available fields in my table which I have yet to utilize on my screen, and the right side shows what fields I've already added to my screen.

- Ashur

The basic structure for the grid is as follows:

FOR all the customers in the customers table

SHOW the FullName in the first column of the grid

SHOW the Certification columns in the next columns of the grid, based on filters outside of the grid for Year and Quarter.

Make the table editable so that all customers, whether they are in the Certification table or not, are shown and then saving populates their records in the cCertifications table for that particular Year and Quarter.

I am unable to show ALL customers and their associated attributes in the grid. In Access, something like a Join or Select query would allow me to do this but I can't seem to be able to do this in Method.

Understood, you'll still need to add a record into your custom table for each customer. You can do this by looping through your Customer table, retrieving the FullName field from your Certification table, use a conditional statement to check whether or not the customer name already exists in your Certification table, and finally insert the records into that table. Below is my example.

*Note: If you have any required fields in your custom table, ensure you're adding entries into those fields as well when editing the Insert Records Into Table action.

In order to modify the entries in your grid just edit the screen and make your grid editable. Everytime you modify a record click the Update button to save the changes.

- Ashur