Natural Solutions Blog

Use Cyberquery to get a list of PROSPECTS

Natural Solutions - Tuesday, December 29, 2015

So, Natural Order has a list of names in the NAMES table, and a list of orders in the ORDERS table - but how can I get a list of everyone who has never ordered?

To do that, you will write a report on the lack of information; i.e., a list of NAMES with no ORDERS.  This is always a fun task, because how do you write a report on something that is not there?

It turns out to be fairly straightforward, where you will learn some interesting things along the way; here's the recipe:

  1. Create a new LIST report in Cyberquery
  2. Set the domain to NAMES (more advanced users should choose NAME_AND_ADDRESS)
  3. From the NAMES folder on the left, drag NAME_ID, FIRST_NAME, and LAST_NAME to the LIST box
  4. You will get a report - wait for it to run
  5. Note how many records are selected (look at the lower right corner of the screen to see the number of records SELECTED)

You now have a list of ALL names; lets see if each name has any orders.

  1. On the left side, open up the yellow folder named V_NAME_ORDER_RANGE

This is a very useful table that has PER NAME_ID the number of orders, the first & last order date, and the first & last order number.  Note that it ONLY has a row IF THERE HAS BEEN AT LEAST ONE ORDER...

  1. Drag the NAME_ID in V_NAME_ORDER_RANGE to the LIST box

Notice how the number of records selected has now dropped, as you are now ONLY displaying the NAMES that have ORDERS - this is the default for Cyberquery, called an OUTER JOIN.  We need to change the report to show those WITH and WITHOUT orders.

  1. Right-click on the V_NAME_ORDER_RANGE:NAME_ID in the LIST box, and choose EDIT EXPRESSION
  2. Change the phrase V_NAME_ORDER_RANGE:NAME_ID to be (+)V_NAME_ORDER_RANGE:NAME_ID - you are really just inserting the (+) symbol to the front
  3. Click OK

Adding the (+) means to show NAMES regardless of whether they have V_NAME_ORDER_RANGE rows or not, so the number of records selected should now show the total count again of your names instead of only those with orders.

Additionally note that those rows WITHOUT a NAME_ID in V_NAME_ORDER_RANGE - those are your NAMES without any orders; i.e., your prospects.  We need to change the report to only select those NAMES without a NAME_ID in V_NAME_ORDER_RANGE.

  2. CLICK in the THIRD ITEM where it says "Click here and type"...
  3. Type the word null (no quotes) - this word means THERE IS NO INFORMATION / IT DOES NOT EXIST
  4. Click OK

You now have a list of NAMES without orders - your prospects.  Ta-dah!