Solved

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

Posted on 2014-12-17
5
120 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
Comment Utility
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
Comment Utility
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 24

Accepted Solution

by:
Will Loving earned 300 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

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…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now