Solved

FM - Efficient way of Displaying Section, Block, & Lot from Repeating Fields

Posted on 2014-12-17
5
135 Views
Last Modified: 2014-12-17
Below is a case statement I wrote to list the section, block, and lot in our rental agreements.  First, it is not very efficient, but second, I need to not show Section if it is the same as the previous repetition.  For example, Section, Block, and Lot for a property might look like this:
Section  Block   Lot
56           352       6
56           352       7
57           353       2
57           353       3
58           200       1

How would I change the statement below so that the repetition of section does not show up.  The above output should read, "Section 56, Block 352, Lots 6,7; Section 57, Block 353, Lots 2,3; Section 58, Block 200, Lot 1.

I could write an IF statement that If Section[2] = Section[1], then leave Section[2] out, but note below how many times I would have to do this.  Is there a simpler way than about a hundred IF statements?

Case (

 not IsEmpty ( Section[10]);
     "  (Section " & Section[1] & ", Block " & Block[1] & ", " & "Lot " &  Lot[1] & "; " &
     "Section " & Section[2] & ", Block " & Block[2] & ", " & "Lot " &  Lot[2] & "; " &
     "Section " & Section[3] & ", Block " & Block[3] & ", " & "Lot " &  Lot[3] & "; " &
     "Section " & Section[4] & ", Block " & Block[4] & ", " & "Lot " &  Lot[4] & "; " &
     "Section " & Section[5] & ", Block " & Block[5] & ", " & "Lot " &  Lot[5] & "; " &
     "Section " & Section[6] & ", Block " & Block[6] & ", " & "Lot " &  Lot[6] & "; " &
     "Section " & Section[7] & ", Block " & Block[7] & ", " & "Lot " &  Lot[7] & "; " &
     "Section " & Section[8] & ", Block " & Block[8] & ", " & "Lot " &  Lot[8] & "; " &
     "Section " & Section[9] & ", Block " & Block[9] & ", " & "Lot " &  Lot[9] & "; " &
     "Section " & Section[10] & ", Block " & Block[10] & ", " & "Lot " &  Lot[10]
;
 not IsEmpty ( Section[9]);
     "  (Section " & Section[1] & ", Block " & Block[1] & ", " & "Lot " &  Lot[1] & "; " &
     "Section " & Section[2] & ", Block " & Block[2] & ", " & "Lot " &  Lot[2] & "; " &
     "Section " & Section[3] & ", Block " & Block[3] & ", " & "Lot " &  Lot[3] & "; " &
     "Section " & Section[4] & ", Block " & Block[4] & ", " & "Lot " &  Lot[4] & "; " &
     "Section " & Section[5] & ", Block " & Block[5] & ", " & "Lot " &  Lot[5] & "; " &
     "Section " & Section[6] & ", Block " & Block[6] & ", " & "Lot " &  Lot[6] & "; " &
     "Section " & Section[7] & ", Block " & Block[7] & ", " & "Lot " &  Lot[7] & "; " &
     "Section " & Section[8] & ", Block " & Block[8] & ", " & "Lot " &  Lot[8] & "; " &
     "Section " & Section[9] & ", Block " & Block[9] & ", " & "Lot " &  Lot[9]
;
 not IsEmpty ( Section[8]);
     "  (Section " & Section[1] & ", Block " & Block[1] & ", " & "Lot " &  Lot[1] & "; " &
     "Section " & Section[2] & ", Block " & Block[2] & ", " & "Lot " &  Lot[2] & "; " &
     "Section " & Section[3] & ", Block " & Block[3] & ", " & "Lot " &  Lot[3] & "; " &
     "Section " & Section[4] & ", Block " & Block[4] & ", " & "Lot " &  Lot[4] & "; " &
     "Section " & Section[5] & ", Block " & Block[5] & ", " & "Lot " &  Lot[5] & "; " &
     "Section " & Section[6] & ", Block " & Block[6] & ", " & "Lot " &  Lot[6] & "; " &
     "Section " & Section[7] & ", Block " & Block[7] & ", " & "Lot " &  Lot[7] & "; " &
     "Section " & Section[8] & ", Block " & Block[8] & ", " & "Lot " &  Lot[8]
;
 not IsEmpty ( Section[7]);
     "  (Section " & Section[1] & ", Block " & Block[1] & ", " & "Lot " &  Lot[1] & "; " &
     "Section " & Section[2] & ", Block " & Block[2] & ", " & "Lot " &  Lot[2] & "; " &
     "Section " & Section[3] & ", Block " & Block[3] & ", " & "Lot " &  Lot[3] & "; " &
     "Section " & Section[4] & ", Block " & Block[4] & ", " & "Lot " &  Lot[4] & "; " &
     "Section " & Section[5] & ", Block " & Block[5] & ", " & "Lot " &  Lot[5] & "; " &
     "Section " & Section[6] & ", Block " & Block[6] & ", " & "Lot " &  Lot[6] & "; " &
     "Section " & Section[7] & ", Block " & Block[7] & ", " & "Lot " &  Lot[7]
;
 not IsEmpty ( Section[6]);
     "  (Section " & Section[1] & ", Block " & Block[1] & ", " & "Lot " &  Lot[1] & "; " &
     "Section " & Section[2] & ", Block " & Block[2] & ", " & "Lot " &  Lot[2] & "; " &
     "Section " & Section[3] & ", Block " & Block[3] & ", " & "Lot " &  Lot[3] & "; " &
     "Section " & Section[4] & ", Block " & Block[4] & ", " & "Lot " &  Lot[4] & "; " &
     "Section " & Section[5] & ", Block " & Block[5] & ", " & "Lot " &  Lot[5] & "; " &
     "Section " & Section[6] & ", Block " & Block[6] & ", " & "Lot " &  Lot[6]
;
 not IsEmpty ( Section[5]);
     "  (Section " & Section[1] & ", Block " & Block[1] & ", " & "Lot " &  Lot[1] & "; " &
     "Section " & Section[2] & ", Block " & Block[2] & ", " & "Lot " &  Lot[2] & "; " &
     "Section " & Section[3] & ", Block " & Block[3] & ", " & "Lot " &  Lot[3] & "; " &
     "Section " & Section[4] & ", Block " & Block[4] & ", " & "Lot " &  Lot[4] & "; " &
     "Section " & Section[5] & ", Block " & Block[5] & ", " & "Lot " &  Lot[5]
;
 not IsEmpty ( Section[4]);
     "  (Section " & Section[1] & ", Block " & Block[1] & ", " & "Lot " &  Lot[1] & "; " &
     "Section " & Section[2] & ", Block " & Block[2] & ", " & "Lot " &  Lot[2] & "; " &
     "Section " & Section[3] & ", Block " & Block[3] & ", " & "Lot " &  Lot[3] & "; " &
     "Section " & Section[4] & ", Block " & Block[4] & ", " & "Lot " &  Lot[4]
;
 not IsEmpty ( Section[3]);
     "  (Section " & Section[1] & ", Block " & Block[1] & ", " & "Lot " &  Lot[1] & "; " &
     "Section " & Section[2] & ", Block " & Block[2] & ", " & "Lot " &  Lot[2] & "; " &
     "Section " & Section[3] & ", Block " & Block[3] & ", " & "Lot " &  Lot[3]
;
 not IsEmpty ( Section[2]);
     "  (Section " & Section[1] & ", Block " & Block[1] & ", " & "Lot " &  Lot[1] & "; " &
     "Section " & Section[2] & ", Block " & Block[2] & ", " & "Lot " &  Lot[2]
;
 
"  (Section " & Section & ", Block " & Block & ", " & "Lot " &  
If (  not IsEmpty ( Lot[1] ); Lot[1]; "" ) & 
If (  not IsEmpty ( Lot[2] ); "," & Lot[2]; "" ) & 
If (  not IsEmpty ( Lot[3] ); "," & Lot[3]; "" ) & 
If (  not IsEmpty ( Lot[4] ); "," & Lot[4]; "" ) & 
If (  not IsEmpty ( Lot[5] ); "," & Lot[5]; "" ) & 
If (  not IsEmpty ( Lot[6] ); "," & Lot[6]; "" ) & 
If (  not IsEmpty ( Lot[7] ); "," & Lot[7]; "" ) & 
If (  not IsEmpty ( Lot[8] ); "," & Lot[8]; "" ) & 
If (  not IsEmpty ( Lot[9] ); "," & Lot[9]; "" ) & 
If (  not IsEmpty ( Lot[10] ); "," & Lot[10]; "" )
 
)
0
Comment
Question by:rvfowler2
  • 2
  • 2
5 Comments
 

Assisted Solution

by:Dennis Burnham
Dennis Burnham earned 200 total points
ID: 40505152
I think you should explore the LIST function.  Assuming that each of these locations is its own record in the database, the LIST function will give you a simple, return-delimited list of all of them.

Maybe I misunderstand and what you want to do is display in the rental agreement the specific location of a certain car.  If that's the case, it's a different solution than using LIST, but also a lot easier than what you've done with the CASE statement.
0
 
LVL 2

Author Comment

by:rvfowler2
ID: 40505210
The List wouldn't work because their are actual numbers in these repeating fields in order to be absolutely clear on our layout.  However, when we export to Word to write a lease, we don't want the same Section # mentioned repeatedly.
0
 
LVL 25

Accepted Solution

by:
Will Loving earned 300 total points
ID: 40505220
Randy - I think Dennis is correct that your problems will require use of the List() function but the real solution may be converting your Repeating Fields to a table. Repeating Fields definitely have their uses but performing summarizations on them can be challenging. If each Section/Block/Lot line was a record, then you could setup a self-join relationship based on Property+Section+Block and then create a calculation that summarizes all related records and only displays a result for the first record of the related records. You would use the List() function within that calculation to get the list of related lots.
0
 

Expert Comment

by:Dennis Burnham
ID: 40505222
I have some time today, so if you want to send me the database with enough info to be able to help, I would not mind taking a crack at it for you.
0
 
LVL 2

Author Closing Comment

by:rvfowler2
ID: 40505266
Yes, I've converted several other repeating fields into a sub-table but have avoided this one for a while.  Looks like it's finally necessary.  Thanks.  I like the solution.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Pop up windows can be a useful feature of any Filemaker database.  Though best used sparingly, they can be employed in a multitude of different ways, for example;  as a splash screen at login, during scripted processes to control user input, as pick…
Having just upgraded from Filemaker 11 to Filemaker 12 over the weekend, we thought we would add some tips for others making the same move.  In general, our installation went without incident. Please note that this is not a replacement for Chapter 5…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

791 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question