Highlighting of double entries in a portal in FileMaker Pro
Imagine a situation where you want to be alerted when a zipcode "houses" more than one of your contacts. This could be of course correct information (they are really located in the same building or have their offices in their immediate neighbourhood) or you might have entered the contact twice into your database. This scenario is based on the assumption that your zipcodes are very "tight", i.e. they depict very precisely a building, possibly in conjunction with a house number. This is the case in the Netherlands where a code + a house number are enough to locate any house or even apartment: 1234 AB would be a zipcode, 567 the house number, and the combination results in a unique identification. You can of course adapt the scenario described here to your needs and possibilities in selecting duplicates.
I devised a way in alerting the user of such double entries by showing and highlighting them.
Portal with organisations that share the same zipcode.
The first row is always the current organisation. The other rows show in alphabetical order.
Here's how to do it:
- Place a portal on your layout, transparent, black outline, (in this case) 5 rows, scroll bar.
- Create a field t_zipcode.
- Create a selfjoin relationship on t_zipcode.
- Place the field t_organisation_name or whatever name the field with the main name of your contact has, into the first portal row. Funnily and luckily, the sequence of the portal rows is always the current record in the first row and then the others in alphabetical order (no sorting set in the relationship).
- Create a global containerfield g_zipcode_colouring. Set it to enlarge and center. Give it a black outline with the same thickness as the outline of your portal.
- Create a bright yellow 1 pixel.
- Copy the pixel in layout mode.
- In Browse mode, paste the pixel into g_zipcode_colouring.
- Remove the field g_zipcode_colouring from your layout.
- Create 4 calc fields (result = graphic):
c_row_2:
Case(
Count(selfjoin_zipcode::t_organisation_name)<=1;"";
Count(selfjoin_zipcode::t_organisation_name)=2;g_zipcode_colouring;""
)
c_row_3:
Case(
Count(selfjoin_zipcode::t_organisation_name)<=1;"";
Count(selfjoin_zipcode::t_organisation_name)=3; g_zipcode_colouring;""
)
c_row_4:
Case(
Count(selfjoin_zipcode::t_organisation_name)<=1;"";
Count(selfjoin_zipcode::t_organisation_name)=4; g_zipcode_colouring;""
)
c_row_5_and_beyond:
Case(
Count(selfjoin_zipcode::t_organisation_name)>=5; g_zipcode_colouring;""
)
- Make c_row_2 exactly the size of row 2.
- Make c_row_3 exactly the size of rows 2+3.
- Make c_row_4 exactly the size of rows 2+3+4.
- Make c_row_5_and_beyond exactly the size of rows 2+3+4+5.
- Place all four calc fields under row 2 so they form an overlap and send them all to the back. You MUST let all 4 fields protrude a few pixels to the LEFT of the portal frame. This protrusion is then covered with a rectangle of the layout's basis colour. By introducing the protrusion you prevent the next empty row to be highlighted, too (FileMaker quirk).
If there are more than 5 rows filled and you scroll down the portal, ALL rows will be highlighted by the yellow colour which physically was laid only under row 5! Nice quirk, too...
Good luck!
Back to the list of my FileMaker Pro trouvailles
|
