1 Reply Latest reply on Oct 5, 2010 11:32 AM by philmodjunk

    'Existing/Unique' field help

    TomGrasmeyer

      Title

      'Existing/Unique' field help

      Post

      I have a DB which I use for selling ads in a publication I'm designing. It manages what ad is sold to what advertiser. There are some ad positions that cannot be sold more than once in a publication (inside front cover, outside back cover, inside back cover). I'll call these 'unique positions.' All others can be sold multiple times (1/2 page, full page, etc.). So I want the DB to make sure that for each publication I have not sold these 'unique positions' more than once. I have two tables:

      Table 1: Ad types - a list of all the possible ad types available for sale. Fields include: ad ID, ad description, ad size, and then a 'check box' for 'unique position' (on or off).

      Table 2: ad bookings - the DB into which all the ad orders are entered. Fields include: booking ID, advertiser ID, ad ID and the various lookup fields to table 1 based on a relationship linking 'ad ID' field (portal).

      So for 'ad ID' in table 2, I need to 'validate' the field on entry: I want it to look at the ad ID and determine if it's a 'unique position.' If it's a unique position, I want it to look through the DB and see if that space is already sold (to see if, for that ad ID, the number of times it appears = 1). If it IS already sold (so that ad ID already appears 1 time) it needs to come back to the user and say 'sorry, that ad is already sold.' If it it NOT already sold, it needs to allow the entry.

      Any suggestions on how to do this?

        • 1. Re: 'Existing/Unique' field help
          philmodjunk

          How do you identify which publication an Ad Bookings record is for?

          You can define a self join that links ad bookings to a second table occurrence of the same table by ad type, booking ID, and whatever field identifies the publication to which this booking record belongs.

          AdBookings::AdType = BookingCheck::AdType AND
          AdBookings::PublicationID = BookingCheck::PublicationID AND
          AdBookings::BookingID ≠ BookingCheck::BookingID

          For ad types such as Front Cover, Not IsEmpty ( BookingCheck::BookingID ) means another record for the same publication has already been created for the front cover.

          To create a "second table occurrence", click AdBookings in Manage | Database | Relationships to select it then click the button in the lower left corner with two green plus signs.

          If "table occurrence" is a new term see this link for more on what they are and how you can use them in FileMaker:  

          Tutorial: What are Table Occurrences?