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!