Formatting Layout to Display Customer Status

[Jarvis]

At my cabinet shop we rely on a chart like the attached one to keep track of customer status.

The possible statuses are:  NOW - NEXT - SOON - MAYBE

We currently generate this chart using CAD software.  Is there a way in Filemaker to format a layout that would present the information in a similar format?

 

 

[Ocean West]

Four fields with the calculations:

ExecuteSQL ( "SELECT customer FROM jobs WHERE status = ? ORDER BY customer" ; ""; "" ; "now"  )

ExecuteSQL ( "SELECT customer FROM jobs WHERE status = ? ORDER BY customer" ; ""; "" ; "next"  )

ExecuteSQL ( "SELECT customer FROM jobs WHERE status = ? ORDER BY customer" ; ""; "" ; "soon"  )

ExecuteSQL ( "SELECT customer FROM jobs WHERE status = ? ORDER BY customer" ; ""; "" ; "maybe"  )

OR

portals that are filtered based on "status" 

[Jarvis]

Thank You for your help!

[LaRetta]

On 12/24/2015 at 1:31 PM, Ocean West said:

Four fields with the calculations:

ExecuteSQL ...

OR

portals that are filtered based on "status" 

Before using ExecuteSQL() in calculations, you might wish to read this thread ... note Wim's last entry:

http://fmforums.com/topic/90123-executesql-custom-function/?do=findComment&comment=413704

Portal filtering isn't light-weight either but I would choose that easily over ExecuteSQL() calculations.   I would make an assumption that your customers table would hold a large number of records and portal filtering on large tables can be slow, particularly LAN.  I should think the ideal would be to simply generate a sub-summary report (open a new window if you wish) which would list them as:

Now
Holme
Maxwell

Next
Smith
Johnson
etc

You haven't indicated how this will be used.  Obviously you don't need up-to-the-second dynamic display of customers because you currently use CAD software to generate it.  Is this for display in browse on layout or as report?  Served to iOS?  How this list is displayed/used would help us fine-tune our suggestion.

You can also use ExecuteSQL() as Stephen indicates to script setting four global fields with the calculation results.  If you are on Customers layout just be sure your record is committed first.  However, since this appears to be a solid, permanent part of your structure, I would be inclined to add the paraphernalia and make it permanently relational.  In this way, you can take advantage of the relationships in other ways such as conditional value lists, GTRR to your lists, counting each group, etc.  

Attached in fp7 format (so those with pre12 versions can access it).  You can use portal or simply set globals.  If you use the script I provided, please note that the table occurrence name is hard-coded so if you change the TO name it will break.  Hard-coding is not recommended but 1) it was simpler for me to create for this example and 2) sometimes it is worth it if you are connecting to multiple 'identical' table occurrence names and it is clearly documented about potential dangers of renaming those TOs.

In all, I hope this gives you additional options so you can make the decision based upon your current needs. 

ColumnarCustomers.fp7.zip