Jump to content
CKonash

Conditional Formatting Based on Records from Another Table

Recommended Posts

Hello,  I am not a developer so excuse me for asking something that might seem simple. 

I manage a filemaker database for a fire department.  There are many tables but the ones I am referring to are "Incident Reports" and "Pre Plans"

Chiefs or firefighters enter the address of an incident into a new record in "Incident Reports".  On that layout is a button that will take the address and perform a find for that address in the "Pre Plan" table and tell you if that address has a record.  In Pre Plans we store address specific info like electric shutoff and other things.  

My question is:  How do I get the button on the "Incident Report" To change color after someone enters an address?  

I would like it to be RED if there is no related record and Green if there is a record present for that address.  

I already have a relationship linking IncidentReport::Address to PrePlan::Address.  

Thank you for your time!. 

 

Chris

Share this post


Link to post
Share on other sites

something like this?

Button State.fp7

 

I did this in 11 because i don’t have 12 on this computer. Just drop the file on the 12 application in your dock

Share this post


Link to post
Share on other sites

Hi Chris,

As Lee indicates, you test to see if the value exists.  The value you are looking for, from the perspective of IncidentReport, will be looking through the relationship to PrePlan so your conditional formatting would be something like this:

IsEmpty ( PrePlan::Address )

By the way, this is prime case where one can apply branch prediction.  Will the majority of IncidentReport records lack a related PrePlan address?  If so, color your button initially red since that will affect most of the records then apply not IsEmpty ( PrePlan::Address ) to turn it green.  Or reverse the logic.  Either way, one color of the button should exist and the other be conditionally applied.  There is no need to apply both conditions.

Share this post


Link to post
Share on other sites

Thank you for the replies.  

 

I entered that formula into the conditional formatting area of the button that runs the script to perform the find in the PrePlan records.   I see the button changing colors as I scroll through the data records in the Incident Report Records, however there is no rhyme or reason to when it changes.  Its "Red" on incident reports with addresses that definitely have preplans already and it's green most of the other times.   

I wonder if it's the type of relationship since it's not a number key field or something.  The relationship is a simple relationship of when one address directly matches the same address in the other table.  Wouldn't an extra space in the address field in one table break the relationship? 

 

 

Share this post


Link to post
Share on other sites
Posted (edited)
1 hour ago, CKonash said:

Its "Red" on incident reports with addresses that definitely have preplans already and it's green most of the other times.

If the relationship is valid, IOW, if you can place PrePlan address on your IncidentReport layout and it displays an address, then the conditional format should work fine.  It sounds like you have it backwards.  What color is the button normally?  Green or red?  What is the exact formula you've entered in the conditional format and what color is specified?

The key on both sides of a relationship should match same data-type and since it is address, both should be text.

 

1 hour ago, CKonash said:

Wouldn't an extra space in the address field in one table break the relationship? 

Yes, a space will break the relationship.

BTW, this would have nothing to do with a find that you perform from the button.  If there are no related PrePlan records, your find would fail.  This involves only display of color on a button, regardless of the action the script performs.

What you might wish to do is run a Replace Field Contents on the address field (after showing all records), with formula of:

TrimAll ( PrePlan::Address ; 0 ; 0 )

And repeat on IncidentReport table.  Then see what kind of matching you get.  I suggest an Auto-Enter ( Replace) be established to remove spaces but ... you really shouldn't be matching on the exact address anyway because its value can change and that will break the relationship.  You should be using a unique ID for the address to join your relationship. Under normal circumstances, you would use an address's uniqueID to join your relationship.

Edited by LaRetta

Share this post


Link to post
Share on other sites

Also, Replace Field Contents can not be undone so always back up your file before running it through records.

Share this post


Link to post
Share on other sites

Thank you LaRetta.

I wish I could relate the addresses in the two different tables via a unique ID. Unfortunately here's why not.
The Preplans were created with an excel export from our township tax dept, now we have a preplan record for each taxable address in our town, 10,000ish records.
The Incident Reports are populated from an email that is pushed out from our police dept computer system with there is a call. The email is parsed out using a script and 360works that I paid a developer to help me with.
I have no control over the police computer system and therefore can't get a unique address ID from them for each call to join as a relationship to our preplans.
So unless I'm not approaching it in the correct manner I don't really see a way to relate the records except for the address itself.
I'll backup the database when I get home and try the replace "trim" Calc and see where that gets me.

Thanks everyone for your help!


Sent from my iPhone using Tapatalk

Share this post


Link to post
Share on other sites
1 minute ago, CKonash said:

The Preplans were created with an excel export from our township tax dept ... The Incident Reports are populated from an email that is pushed out from our police dept computer system ... I have no control over the police computer system ...

Ah yes, that makes sense since we all ( at times ) face issues where we cannot control the data we receive.

I corrected my above statement, since such a rigid statement is not accurate:    

From:  You should be using a unique ID for the address to join your relationship.

To:  Under normal circumstances, you would use an address's uniqueID to join your relationship.

Share this post


Link to post
Share on other sites

Matching by address can work only if the user filling out the report enters the address exactly as it appears in PrePlan. Any discrepancy - e.g. "Rd." vs. "Road", or "N." vs. "North" will trip this up.

Share this post


Link to post
Share on other sites

I thought that would have been clear in this case since Chris knew a space might break it, but you are right that it might not be clear for everyone viewing this thread so it was certainly worth mentioning, and I should have done it!  Thank you, Michael.

Share this post


Link to post
Share on other sites

Thank you. A way we are trying to control data input is to have people filling out incident reports manually to pull all the street names from a value list instead of them typing it in manually.
I will play with the formatting with the 'trim' Calc a little later tonight.
Hopefully we can figure out a way to get the button that finds the related preplan to change from red to green if there is a related record present for that address.

Thanks agin for all the help.


Sent from my iPhone using Tapatalk

Share this post


Link to post
Share on other sites

For valid relationships ( those that match ) then what I said above should work fine which is ... make the button on your IncidentReport layout red then create conditional format on the button with:

not IsEmpty ( PrePlan::Address )

... and specify green for the fill color based upon the understanding that the field PrePlan::Address is the key field in the relationship or is never empty.  :-)

Share this post


Link to post
Share on other sites

But what would be the purpose of such button?

Share this post


Link to post
Share on other sites

Odds are, you will have many unmatched records.  Data-entry is rarely ever a match particularly on street addresses, as Comment points out.  

Share this post


Link to post
Share on other sites

Awesome. Thank you very much!


Sent from my iPhone using Tapatalk

Share this post


Link to post
Share on other sites

Hi Michael,

I assumed ( wrong of me, I know ) that the button would simply indicate that a search must take place to try and find the PrePlan match for the Incident Report address.  So if the button is red, clicking it would perform an advanced find in PrePlan for the right address.

Share this post


Link to post
Share on other sites
7 minutes ago, LaRetta said:

So if the button is red, clicking it would perform an advanced find in PrePlan

If that is the case, I would suggest doing this automatically and let the user select the correct address from among those found and match it by its ID.

Share this post


Link to post
Share on other sites

So would I!  I started to go into details about an advanced search and presenting User with possible matches for selection but I decided to see if Chris wanted to go that route after realizing substantial number of non-matches.  But that seemed a separate issue from colorizing a button which would simply show whether the IncidentReport record has found its match without intervention.

As usual, you are ( rightfully so ) ahead of me.  :smile3:

Share this post


Link to post
Share on other sites

Hi, 

Thanks everyone for their comments and experience.  

I would love for every address that will be used on an Incident report to already be in a list somewhere however we have new addresses pop up all the time and our calls for service very often reach outside of our town, county, and state so there is just no way for us to gather all those addresses and maintain a correct database.  We have decided to do what we can to make our own townships addresses our priority and having them exported from the Tax dept was the easiest way to get a good capture of each and every address.   

You both bring up very valid points and I may see if the Police department can give us the same full export from their dispatch system of each address, but that would would carry a unique ID with each address record.  That might be a better way to do this.  

 

But yes the simple colored button was an easy way for a chief officer on the scene of a call to see if there was a Preplan record that matched that address.  the button could still be pushed to trigger the find script but then you would simply get "no records found". 

One more thing is I have the button do a search for the numeric and street address only.  Since some of our commercial businesses all operate under the same address using the "Colored Button" I have a layout "list view" of all the addresses that match that and then it lists the suite or unit number.  This way the commanding officer of an incident can search an address and see all the associated business inside that address and then they each have their own PrePlan record under that same address.  Not sure if that made sense or not.  Sorry. 

 

Thanks

Chris

 

Share this post


Link to post
Share on other sites

No need to be sorry at all, Chris.  :-)

These types of discussions ( should ) take place in almost every solution.  Usually the discussion involves names, such as when seeing if a Contact already exists before allowing creation of new contact.  

Share this post


Link to post
Share on other sites

Hi All.  

I did the TrimALL field replacement on the PrePlan Address Records, Incident Report Address Records, and out Street List value list (to fix future problems) and everything is working AWESOME.  Thank you so much for your experience and help!.  

There must have been extra spaces everywhere which was breaking the relationships...  

I would never have figured that out without your help!. 

 

Thanks Again!. 

Chris

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now


  • Who Viewed the Topic

    1 member has viewed this topic:
    memond 
×

Important Information

By using this site, you agree to our Terms of Use.