Solved

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

Posted on 2014-12-17
5
132 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Problem: You have a hosted FileMaker database and users are tired of having to use Open Remote or Open Recent to access the database. They say, "can't you just give us something to double-click on rather than have to go through those dialogs?" An…
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 Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

773 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