Tag Archives: Programming

FileMaker: One to Many Reports

Leave a reply

FileMaker is great for putting together a quick form for any number of data-entry chores.  By using a FileMaker portal you can put together a master/detail form that will keep track of transactions based on some kind of header record.  Typical examples include:

  • Invoices and line items
  • Customers and interactions
  • Prospects and sales efforts.
  • Jobs and application sequence.

These forms are especially helpful when you need to keep track of a “pipeline”.   For example when applying for a job, there are multiple steps involved:

  • Applied for the job
  • Received an eMail acknowledgement
  • First phone interview
  • Second phone interview
  • Scheduled live interview

At any one time you may have multiple jobs somewhere in the process, and a typical status report would show a list of each job and its current status.

This brings us  back to the question of displaying the jobs and activities. We’re looking for a “report” which has a list of jobs, and underneath each job the list of activities that have taken place for the job.

JobHunt FileMaker portals display the related records for each master record but a portal almost by definition shows only a specific number of transactions at a time in a scrollable window.  For a printed report, where we want to see all of the related records, we need to define the report without the portal.  This is done using FileMaker’s “sub summary” band when creating the report layout.  The trick is to start defining the report using the transaction table as the basis of the report displaying the fields in the body band and referencing the master table in the sub summary band.  When setting this up,  it looks something like this:

JobHunt2.png

Note the report bands on the extreme left,  with the body band at the bottom, the sub summary above it,  and the report header band at the top.   One clue that the basis of the report is the transaction table is that at the top of the screen, it shows “Table:Transactions” .  Also, the reference to the related field in the subsummary band are prefaced with the double colonon.

The ::RprtHeader  field is actually a calculated field which consists of the employer’s name, and the job being advertised by the employer.  This solves the problem of applying to more than one job at a single employer, in that it effectively provides a unique field name to display the transactions for just one job at a time.

 

 

Get-StateAbbreviation

Leave a reply

Here is an updated version of my state abbreviation function, with changes prompted by Jeffery Hicks.
I’ve also added the Canadian provinces to the mix. The main change (apart from changing the name of the function to conform to the conventional Powershell verb-noun nomenclature), is to add the CmdletBinding section to specify a single parameter which needs to be supplied to run the function. The neat thing about this is that if you issue the command without the parameter it will automatically prompt for input. 

function Get-StateAbbreviation {
#
# Takes upper, lower, or mixed case state name
# and return the two-letter abbreviation. 
# LK 3/17/15  rev. 3/26/15 per Jeffery Hicks 

# Still To Do: 
#   1. Full documentation 
#   2. Allow input from the pipeline 
#   3. Place in a module 

# Example calls: 
#   Get-StateAbbreviation Vermont     <-don't use parentheses
#   Get-StateAbbreviation -StateName Vermont
#   Get-StateAbbreviation "Vermont"
#   Get-StateAbbreviation $MyStateName 
# Note that this function has to appear before it is called in the code
# if it isn't part of a module. 

[CmdletBinding()]
param (
[Parameter(Mandatory=$True)]
   [string]$StateName
)

Switch ($StateName.ToUpper()) {

($StateName="ALABAMA")        {$shortenState="AL"}
($StateName="ALASKA")         {$shortenState="AK"}
($StateName="ARIZONA")        {$shortenState="AZ"}
($StateName="ARKANSAS")       {$shortenState="AR"}
($StateName="CALIFORNIA")     {$shortenState="CA"}
($StateName="COLORADO")       {$shortenState="CO"}
($StateName="CONNECTICUT")    {$shortenState="CT"}
($StateName="DELAWARE")       {$shortenState="DE"}
($StateName="FLORIDA")        {$shortenState="FL"}
($StateName="GEORGIA")        {$shortenState="GA"}
($StateName="HAWAII")         {$shortenState="HI"}
($StateName="IDAHO")          {$shortenState="ID"}
($StateName="ILLINOIS")       {$shortenState="IL"}
($StateName="INDIANA")        {$shortenState="IN"}
($StateName="IOWA")           {$shortenState="IA"}
($StateName="KANSAS")         {$shortenState="KS"}
($StateName="KENTUCKY")       {$shortenState="KY"}
($StateName="LOUISIANA")      {$shortenState="LA"}
($StateName="MAINE")          {$shortenState="ME"}
($StateName="MARYLAND")       {$shortenState="MD"}
($StateName="MASSACHUSETTS")  {$shortenState="MA"}
($StateName="MICHIGAN")       {$shortenState="MI"}
($StateName="MINNESOTA")      {$shortenState="MN"}
($StateName="MISSISSIPPI")    {$shortenState="MS"}
($StateName="MISSOURI")       {$shortenState="MO"}
($StateName="MONTANA")        {$shortenState="MT"}
($StateName="NEBRASKA")       {$shortenState="NE"}
($StateName="NEVADA")         {$shortenState="NV"}
($StateName="NEW HAMPSHIRE")  {$shortenState="NH"}
($StateName="NEW JERSEY")     {$shortenState="NJ"}
($StateName="NEW MEXICO")     {$shortenState="NM"}
($StateName="NEW YORK")       {$shortenState="NY"}
($StateName="NORTH CAROLINA") {$shortenState="NC"}
($StateName="NORTH DAKOTA")   {$shortenState="ND"}
($StateName="OHIO")           {$shortenState="OH"}
($StateName="OKLAHOMA")       {$shortenState="OK"}
($StateName="OREGON")         {$shortenState="OR"}
($StateName="PENNSYLVANIA")   {$shortenState="PA"}
($StateName="RHODE ISLAND")     {$shortenState="RI"}
($StateName="SOUTH CAROLINA")   {$shortenState="SC"}
($StateName="SOUTH DAKOTA")     {$shortenState="SD"}
($StateName="TENNESSEE")        {$shortenState="TN"}
($StateName="TEXAS")            {$shortenState="TX"}
($StateName="UTAH")             {$shortenState="UT"}
($StateName="VERMONT")          {$shortenState="VT"}
($StateName="VIRGINIA")         {$shortenState="VA"}
($StateName="WASHINGTON")       {$shortenState="WA"}
($StateName="WEST VIRGINIA")    {$shortenState="WV"}
($StateName="WISCONSIN")        {$shortenState="WI"}
($StateName="WYOMING")          {$shortenState="WY"}
($StateName="WASHINGTON DC")    {$shortenState="DC"}
($StateName="ALBERTA")               {$shortenState="AB"}
($StateName="BRITISH COLUMBIA")      {$shortenState="BC"}
($StateName="MANITOBA")              {$shortenState="MB"}
($StateName="NEW BRUNSWICK")         {$shortenState="NB"}
($StateName="NEWFOUNDLAND")          {$shortenState="NL"}
($StateName="LABRADOR")              {$shortenState="NL"}
($StateName="NORTHWEST TERRITORIES") {$shortenState="NT"}
($StateName="NOVA SCOTIA")           {$shortenState="NS"}
($StateName="NUNAVUT")               {$shortenState="NU"}
($StateName="ONTARIO")               {$shortenState="ON"}
($StateName="PRINCE EDWARD ISLAND")  {$shortenState="PE"}
($StateName="QUEBEC")                {$shortenState="QC"}
($StateName="SASKATCHEWAN")          {$shortenState="SK"}
($StateName="YUKON")                 {$shortenState="YT"}

Default {$shortenState="XX"}

} # Switch 

return $shortenState

} # Function

Debugging in PowerShell

Leave a reply

The PowerShell integrated scripting environment (ISE) has many of the trappings of a full-fledged programming GUI, including a capability for debugging with breakpoints. There is a TechNet article on debugging from which this discussion is cribbed.  The ISE has a subset of the command-driven breakpoint capability, in that it only allows the setting of line breakpoints. In the command environment, you can also set variable breakpoints, which execute when the value of a variable changes, and command breakpoints, which execute when a certain command is reached.

Breakpoints can only be set for a script that has been saved.

In the ISE window a breakpoint can be set from the debug menu, (Toggle Breakpoint)  or by pressing F9 when the cursor is on the line that you want to use for the breakpoint.  Once set, the line will be highlighted.

After setting a least one breakpoint, you can run the single-stepper. This executes the script one line at time.

Step Into: Executes the current statement and stop at the next statement. If the statement is a function or script, it goes into the function or script and then stops.  F11

Step Over: Execute the current statement and stop at the next statement.  If the statement calls a function or script, it executes the function or script and then returns to the next statement in the original script. F10

Step Out: Executes the current function and then returns to the level above in the call stack. If there are statements remaining in the sub-function, those are executed before the return. Essentially this is something like “finish running this function, and return…”

Continue: Execute to the next breakpoint without single-stepping.

What I’ve been doing as I’ve been learning PowerShell is single stepping through a script, which allows me to look at the effect of a single statement before moving on to the next statement.  This involves setting a breakpoint a the top of the script, and then hitting F11 to toggle through the script.

Calling sub-scripts.

Scripts can be called from other scripts using the Invoke-Expression commandlet.  Example:

Invoke-Expression -Command ./PSFTPDEMO.ps1

If the subscript is located in the current working directory, or within the same directory as the main script, it needs to be prefaced with the ./ path as shown above.

The subscript inherits all variables from the main script unless those variables are declared private in the main script.

Tech Friday: FileMaker Resources

Leave a reply

Random FileMaker-related resources: 

1. You can create an alternative icon for FileMaker 13. When you have multiple FileMaker versions installed, it sometimes is difficult to distinguish between them. FM12 and FM13 have virtually identical icons with the same color schemes. Here are alternative icons that can be installed, for both Win and Mac from HomeBase Software. HomeBase has a ton of of technical information on their web site.

2. In support of a project to integrate SmartyStreets with FileMaker, I’ve been doing some additional research on JSON, (Javascript Object Notation), which is a simplified version of XML.

3. Coding Standards for FileMaker

4. Modular FileMaker: is shared library of FileMaker functions. There are huge community-developed libraries for other languages such as PHP and Python. These folks are attempting a similar idea for FM. I’ve downloaded their JSON module, and am experimenting with it. Other examples include a nifty SQL query generator, and and another interface to Mailchimp.

Custom Functions are a way of adding small chunks of user-defined code that can be called within a FileMaker script. Brian Dunning is the guru here and curates the largest library of custom FM functions on the web. He also has sample data sets available for the U.S., Austria, Canada, and the UK. Five hundred records are free, and a million records are available for ten bucks.

5. If you need a 100,000 records or so, you could also download the database of public and private schools available at the National Center for Education Statistics. This includes demographic data as well as mailing and location addresses for schools. The data is fun to play around with. You can give yourself some sample exercises in FileMaker. For example:

6. What percentage of public school students are eligible for free or reduced lunch in your state? It is 38% in mine. Poking around in some other states, it looks like that isn’t unusual; in many states it is 40-60% or more. The lunch program is often considered a proxy for the family poverty rate. But maybe that’s another discussion.