[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2014-12-17
5
Medium Priority
?
158 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 

Assisted Solution

by:Dennis Burnham
Dennis Burnham earned 800 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 1200 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

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

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…
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…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Suggested Courses

650 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