• Status: Solved
• Priority: Medium
• Security: Public
• Views: 176

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

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
rvfowler2
• 2
• 2
2 Solutions

CEOCommented:
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

Author Commented:
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

PresidentCommented:
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

CEOCommented:
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

Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.