Jump to content

Formatting Layout to Display Customer Status


Jarvis

This topic is 3014 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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?

 

 

Client Status.jpg

Link to comment
Share on other sites

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" 

Link to comment
Share on other sites

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. :smile3:

ColumnarCustomers.fp7.zip

  • Like 1
Link to comment
Share on other sites

This topic is 3014 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.