Need some help with changing color of field's text based on certain criteria...???

We have an Inventory table that, of course, holds our current inventory.  We also have an Auctions table that stores all of our eBay auction data for inventory that we have posted.  

My goal here is to highlight the ItemNumber of any item in Inventory (list view) that needs an auction posted.

Any item could have a bunch of auction records created for it.  Auction records have a status field with values: Current, Winner, Loser.

I know how to use the TextColor function to change the color of a field's text based on an If/Then, for instance.  But what I need is a calc. field that will be a 1 or 0 based on whether or not there are any current auctions for that item.  If there are current records set NeedsAuctionPosted to a 0.  If there are not any current auctions set NeedsAuctionPosted to a 1.  Then my text color could look at NeedsAuctionPosted to decide whether to change the font color not.

Any information I can get on how to create a field that can search auctions records with the item's ItemNumber and set another field to 1 or 0 based on whether or not any of the current auction records were Current or not would be greatly appreciated.  

Of course, if there's a better way to do this all together I'd love to hear that as well.  Thanks!
LVL 11
Andrew AngellCo-Owner / DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jvaldesCommented:
Set NeedsAuctionPosted to Calculation :

If (Status="Current";1;0)

Then create a summary field : SummaryNeedsAuction Posted to Maximum of NeedsAuctionPosted select the analyze individually


Set the Item Fields to the following calculation

If(auction data::Summary NeedsAuctionPosted=0;TextColor ( Item ; RGB ( 999;0;0 ) );TextColor ( Item ; RGB ( 0 ;0 ; 0 ) ))

That should change the text color to RED when there is no related item in the auction database that contains a record with the status set to Current

I have a database named auction.fp7 that you can download from
 
http://homepage.mac.com/beckyv/

That has a working example, Good luck
0
Andrew AngellCo-Owner / DeveloperAuthor Commented:
I actually made a little bit of progress on my own and was coming back to update.  I've still got problems, though. I did very close to what you said, but not the same.

I created a field in Auctions called AuctionIsCurrent and set it to a calc with exactly what you have:

If (AuctionStatus = "Current" ; 1 ; 0 )

Then, in Inventory I created a calc. field that used Sum...

If ( Sum ( Auctions::Auction_IsCurrent ) > 0 or Qty_Available = 0 ; Ref_Number ; TextColor ( Ref_Number ; RGB ( 255 ; 0 ; 0 ) ) )

So that actually checks to see if any of the auction records for the item are current and also checks the qty to see if we have any.  If none are current and we do have some in stock then it changes it to red.

I'm not quite sure what you were saying with the Summary stuff there.  Is there advantage to do this differently than I have?

I'm trying to do something similar and I'm having a heck of a time getting it to work.  One problem with this method of highlighting the text in these fields is I have to display that calc. field on the layout.  However, this makes that field un-editable.

To try and get around that I decided that instead of highlighted the text in the field a different color I could highlight the field background instead.  Here's what I've done with no luck.

I created a container field called bgGreen.  This field is simply an edit box wit a background filled to green.  It's on a separate layout.  I then created a field called UnitNumberBG and set it as a calc. with the following:

If ( ScanLabelPrinted = 1 ; bgGreen ; "" )

Thinking that I would end up with a green box where I place that field on the layouts we'll be using.  It does not ever change, though, no matter what the value of ScanLabelPrinted is.

Could you possibly a sample for that into your FM file here..??  It'd be GREATLY appreciated.  I cannot figure out what I'm doing wrong.  Thanks!

0
jvaldesCommented:
The only reason I could think of your routine not working is that when you summarized that field you didn't select the individually selection at the bottom of the summary calculation dialog.

I suggest that to change the background color you use a different approach that is not as limited as the one you are choosing,

First of all create a Container field with two repetitions, then save an image of the color that you want tyhe background to change to in each of the repetitions. Next, set the value of the container to either 0 or 1 and the repetition will display (the color or image you selected) what is nice about this approach is that you can put any graphic in the background this way, by just arranging them in the proper order....

If you post your database where I can get it, I can look at it and hopefully I can help. But I think if you follow my notes it will also work...
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

lesouefCommented:
if you'd like a field to be a calc. and still be editable, make it a an auto-enter field based on a calc, and uncheck "do not replace existing value..."
0
Andrew AngellCo-Owner / DeveloperAuthor Commented:
lesouef:

That seems to work on the front end of things, but not the back.  What I mean is say I have a record where ScanLabelPrinted = 0 so the text is currently black.  If I set it to 1 the text does turn green based on being validated by the calc.  However, if I set that back to 0 again it stays green.  The actual calc. field will change back and forth based on the value of ScanLabelPrinted.  Is there a way to do that with validation so I can leave it as an editable field?
0
lesouefCommented:
is it an auto-enter field or a calc. field?
0
Andrew AngellCo-Owner / DeveloperAuthor Commented:
I had it as an actual calculation field type before, but that's when I couldn't edit the field then....at least not without making some workaround that actually takes you to the text field that the calc. field is based on.

After reading your response went from using the calc. field to simply adding the calculation into the Auto-enter and the Validate using calculation portions of the original text field.

With that, it changes to green upon the ScanLabelPrinted field being set to 1, but will not change back when ScanLabelPrinted is set back to 0.

Another problem I have is that with the calc. validation on this text field I can't leave it blank without getting a message telling me the field only accepts certain values and do I want to accept it or not.
0
Andrew AngellCo-Owner / DeveloperAuthor Commented:
I tried fixing the blank value problem by adding a check for that into the validation calc.

If (UnitNumber = "" ; UnitNumber ; If ( ScanLabelPrinted = 1 ; TextColor(UnitNumber ; RGB(0;255;0)) ; UnitNumber ) )

and I also tried

If (UnitNumber = "" ; "" ; If ( ScanLabelPrinted = 1 ; TextColor(UnitNumber ; RGB(0;255;0)) ; UnitNumber ) )

Still getting the message, though, when I try and set the value to blank.
0
lesouefCommented:
1
you must have "do not replace existing value" unchecked for the auto-entered field.
2
also, once a text has been coloured, it stays coloured if you enter something else. to remove the colour, you must specify textcolorremove() or textformatremove() or set another colour like black or whatever.
your line becomes:
If (UnitNumber = "" ; "" ; If ( ScanLabelPrinted = 1 ; TextColor(UnitNumber ; RGB(0;255;0)) ; TextColorRemove(UnitNumber) ) )
0
Andrew AngellCo-Owner / DeveloperAuthor Commented:
Ok, that makes sense with the TextColorRemote thing, thanks!  I checked the box for "do not replace existing value" in Auto Enter but when I do that it doesn't turn green like it should.  :(
0
lesouefCommented:
it should be unchecked! read what I wrote!
0
jvaldesCommented:
I made the modification to the auction database that I created for you before to include changing the color of a block that you can place behind a field that is set to transparent (to give the imprerssion that the field color is changing), with this approach you can set a preferences file where you can update the colors that you get , and if you have many different fields you want to do this with it will reduce the amount of editing you have to do.

It's open so you can see how I did it.

download from the same location I provided before:

http://homepage.mac.com/beckyv/
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Andrew AngellCo-Owner / DeveloperAuthor Commented:
lesouef:

It was unchecked before.  Sorry, I read that as if you were saying, oh, it must be this...that's why it's not working.  I should have read it as it must be checked...like check it cuz you have to.

Anyway, it was like that before.

jvaldes:

thanks, i'm gonna play with this.  be back in awhile.
0
lesouefCommented:
check/unchek: I'll use on/off next time!
ok, so you're only left with the textcolorremove() then
0
lesouefCommented:
just checked: what I said is true for manual entry, not for computed data.
just using
If (flag=1; TextColor ( flag ; RGB (0;0;255 ))  ; flag)
is enough to make it blue if flag=1
any other value leaves it with the default layout colour used for this field text. what is the text colour used in yr layout for this field?
0
Andrew AngellCo-Owner / DeveloperAuthor Commented:
The default color in the layout is black, and the text in that field is indeed black until flag=1.  Once flag=1 it changes to green, but it stays green from this point on no matter what flag is set to.

Also, if the field that has the validation calc. on it is empty then I get a popup message asking if i want to accept the value and I have to say yes.  I'd like to make it accept blank values without that popup.  As I said before, I tried adding another If/Then check to see if the field is empty prior to checking the flat value and changing the text color:

If (UnitNumber = "" ; UnitNumber ; If ( ScanLabelPrinted = 1 ; TextColor(UnitNumber ; RGB(0;255;0)) ; UnitNumber ) )

but that's not working, I still get that popup.
0
lesouefCommented:
ok, dooky, you are using the validation calc!! it should be the auto-enter calc. lave the validation stuff blank
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
FileMaker Pro

From novice to tech pro — start learning today.

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.