Method Community

I was hoping to get some advice on how to find and export customer information based on information in two related tables.

On our Customer screen we have added a custom section that contains software registration information for each individual customer (serial number, version number, etc.). It is linked to the Customer table by the Customer Name field. Now we would like to do a mailing to a portion of the customers based on certain criteria from the two tables. Basically I need to export the addresses of customers that have valid mailing addresses in the US and do not have email addresses listed, but they also need to be on an older version of the software. Since many of our customers have multiple registration records, I need a way to exclude individual customers completely if any of their registrations are for the latest version.

In simplest terms, I have a set representing the customers that meet the mailing criteria, and a second set from the registration table representing the customers that have a specific version (but do not have any records containing the latest version). I would then like to export the intersection of those sets into Excel.

I have considered a few approaches, but thought I would ask for advice first before I go too far down any one path.

Hey,

My first question would be, does it need to exported in spreadsheet format? Or can we use a Generate Report action to extract that data?

- Mortaza

I was going with an Excel format because we will need to supply the mailing addresses to another company, and in the past we always sent them in either a text or Excel format. After export I would also need to be able to go through and do some sanity checking to weed out any addresses that are incomplete or impossible to mail to, so being able to get the data into Excel for sorting would be a must.

Hey,

You have a few routes to take here are a couple:

> Use our built in TagList to tag your customers with their software versions/country of residence/other information. Then using the ListBuilder you can filter by Tags:

It will then create a Grid on the fly with a list of the Customers that fit the filter and you have an option to Export to Excel on the next step.

This route will have some extra overhead in the sense that you would have to use some Excel magic to correctly Tag your customers. Thus not needing to go cell by cell and manually entering the data. You would want to the fields to populate by other fields already on the Table - E.g. for the country-tag you would want that to be populated by BillAddressCountry.

Read more about TagList here: Solution No. 254

Read more about List Builder here: Solution No. 211

> Another route: create a new screen based off your Contacts table. Create a Grid, and using Filter/Views have it listing all the Customers that fit the filter. Once the Grid is listing the Customers that fit the filter use an 'Export Grid to Excel' action to export the list into spreadsheet format.

Let me know which route you are going to take and we can further eloborate on the steps.

- Mortaza

The Tags suggestion is interesting, but this particular search is a one time request. It would be overkill to go through the whole process of importing tags when they would only be used this one time.

It would be more useful to set up a new screen where I could search the customer records for certain criteria and the registrations table for other information, and export the results. This scenario would be used much more frequently since we would always be searching these two tables, but for different situations.

I had actually already created a new screen at some point in the past that had a grid based on the customer table. I didn't see how to incorporate the info from the registration table into the filter or search.

Hey,

You would have to relate the two tables, which will allow us to add Linked Fields from your software Table onto the Customer table.

Start by adding the 'software version' tables RecordID as a dropdown to the Customer table:

on the next step choose the correct Table and your option will be RecordID.

Now that we have the two tables related you can add the rest of the fields as Linked Fields to the Customer table. After you have the full list of fields you need on the Customer table, you can now add those corresponding fields to the Grid.

You can now Filter accordingly and utilize the Export Grid to Excel Action.

Let me know if you need to me elaborate on any of the above steps.

- Mortaza

Thank you for the suggestions. I did what you suggested but the version number was not showing up in my grid. I think that this might be due to the fact that many of our customers have more than one registration record associated with them. We have customers that go back a number of years who have purchased multiple copies and so they might have 5 records listed in the registration table, and they could be for 5 different versions of the software.

I was able to get around this by going the opposite route and using the link that was already present between the registration table and the customer table to add linked fields from my customer table to my registration table.

The grid I created on a new screen works fine for the most part. I have filtered the version number portion of the grid to find only specific versions that are contained in a list (4 and 4.1), so that it does not show records that contain older versions or our current version (4.5).

I just have two remaining problems:

1. If a customer has registration entries for both 4 and 4.5, their entry for version 4 will still be listed in the search result. Is there a way to completely skip over all registration entries for a customer if any of their registration records have version numbers that are equal to 4.5?
2. If a customer does have multiple copies at version 4 or 4.1, they are listed multiple times in the results. Is there a way to just get one customer record listed, regardless of how many registration entries they have that match the search?

Hey,

The reason for that is because you have a many-to-many relationship between the tables (many customers to many versions).

We'll need to go back to 1 to many.

The original setup of Customer to VersionNumber let's go back to that setup. This time in the Grid let's add a Filter to for the VersionNumber:

This should make the Grid from 1 to 1. Please follow up and verify this works for you.

- Mortaza

I have looked at this a bit but I am confused. In your first suggestion, I was adding a Dropdown for the RecordID, and adding the version number as a linked field.
In your latest suggestion, it looks like the version number is now being done via a Dropdown instead of a linked field. The version numbers in my Registrations section are entered in a field, and are not coming from a table, so there isn't an existing version number table to point this new dropdown to.