Method Community

All,

We would like to periodically send e-mail campaigns to people who have purchased something from us. The list builder does allow you to create a list of only those in the Customer table, but that table includes people who may not have actually purchased from us (received only an estimate/quote, converted to customer but not taken rest of steps, etc.).

Ideally, we need a list of people who have paid toward an invoice, but we would probably be satisfied to have a list of those who have received an invoice. Is there some sort of hack that we can perform?

Thanks to all in advance.

Hey Jason,

An option you can take is creating a Grid based off the Received Payments Table. On the Grid you can have field: Customer which will show you a list of all Customers that have Payments against an Invoice.

Now taking this route you will notice you have Customers listed more than once. That is because a Customer can have more than 1 payment.

But using the Loop Through Grid function you can Loop by using distinct values (unique) for Customer:

Which will yield a result that looks like:

*Please note that the list will be dependant on what your Transaction History is set to as per QuickBooks > Synchronize. It'll pull data as far back as the range set there.

Let me know if this works for you, if not we can put together an alternative.

-- Mortaza

Mortaza,

Thanks for your advice. I tried a few things, with no success yet. I'll try to summarize:

1) I hoped to add this option to the "Fill List with Contacts" button (on the default List Builder screen), but it links to a custom HTML page which I cannot edit. So I created a new button on that screen to fill the list with purchasers, copying and editing the actions of the "Fill List with Invoices" button. Instead of looping through the Invoices table, I first loop through the ReceivePayment table to get the customer names from that table (assigning them to an action result as I go). Then before ending the loop, I loop through the customer table to get other customer-specific information. Before ending these loops, I insert a record into the ListBuilder table using the action results I obtained. I then have it refresh the grid and go to the next section. This is all done in the same way that the Invoices button does it. However, the ListBuilder grid does not display any records, even after a manual refresh...

2) Trying to simplify things, I added a linked field to the ReceivedPayments table that points to the customer's e-mail. I created a new button that just loops through the ReceivedPayment table and extracts the customer name and e-mail address, inserts the record into ListBuilder, and shows the next section/step. The grid still does not display any records.

3) After this didn't work, I tried adding a new Section (step) where I created a separate grid based on ReceivedPayment, as you suggested. I set up the new button to loop through this new grid as per your instructions above to remove duplicates, then show the newly created section. However, it displays the grid with all ReceivedPayment records, including duplicates.

Any thoughts?

Thanks.

Update: Today I tried adding a "Delete record from table" action to the button I created to add only purchasers (records from ReceivePayment) to the ListBuilder table. I placed the action first, before any looping or anything else, and it deletes all records (RecordID > 0). I then used the default-provided button to fill the list with contacts and checked that records were there (and they were). I clicked my button, and the result was that the ListBuilder grid was empty. Refresh = no change.

Hey Jason,

I have not tried the ListBuilder route, but if the final outcome is that you are trying to retrieve the list of Emails of the Customers that have payments against an invoice, this is the solution I put together:

~ quick little gif of the screen in action: https://streamable.com/nln8

(1) It looks like you already did but add Email as a linked field onto the ReceivePayment table.

(2) Create a Grid with fields Customer, Email.

(3) Loop Through Grid by Distinct value: Email

(4) Follow THIS post on how to create a loop to append the emails by commas

Please let me know if that works for you if not we can take a further look.

-- Mortaza

OK, I am working on concatenating the text together into one action result as described. Once I get it to work, how can I export that data for use as a contact list? I see only one way to send data to Excel and that's the "Export Grid to Excel." In this case the comma-delimited data is an action result variable, not a grid. Am I missing something?

Thanks.

HI Jason,

I think the purpose of Mortaza's last post was to simply return a list of emails, rather than prepare them for exporting. In Mortazas solution, if you were to have a TextBox display the Action Result, you would then be able to manually copy this list and do with them what you will.

Were you intending for this same screen to send the emails too? Or did you specifically need something that is exportable (ie a grid)

Thanks Jason!

Regards,

Ben

We want to be able to export the list of names & emails for various external uses, including sending our own mass mailings outside of Method.

Hi Jason,

Ive found a way to have this data be entered directly into the ListBuilder table for you. Essentially what Ive done here is create a button and added it to the ListBuilder Screen which Loops through the Contacts table (Because ultimately a list of contacts is what you are looking for, and contains the data relevant to the ListBuilder Table for insertion later). The Loop is searching for all Primary Contacts only (This is one of the QuickBooks synced contacts) by ensuring there is a "Where" clause which specifies that the SpecialType field is equal to "Contact".

Within this Loop, we want to retrieve the Customer_RecordID from the Invoice Table, for where the Customers RecordID associated with an Invoice is equal to the Entity RecordID for the row we are currently looping through

Then, If this Invoice exists (i.e. the Customer_RecordID on the invoice table is >0) we insert the Contact Into the List Builder Table. Ive attached a screenshot containing an Overview of the Logic for reference here.

Keep in Mind if you were using these exact actions on a button added to the ListBuilder screen, it would appear as if nothing happens as it will only be adding the data to the Table. If you want to have this button function like the current "Fill with Contacts" button, you would need to add actions to show the hidden sections of this screen and take you to Section 2 after the loop is finished.

Let me know if you need any more help here Jason.

Regards,

Ben