Use Criteria for a value list

[Kimmie]

Hi All,

I am creating a database for a yearly tradeshow that houses all company and sales information. I have created a separate pricing database. Currently the database pulls the pricing from the other via a sales ID. This works great but there are approximately 106 items to sell. When next years show comes into play I am assuming I have to add another 106 records to my pricing database with the new prices. The sales ID is a drop down list to choose the item, if I add another 106 records, the drop down list is going to start getting ridiculously long. Is there a way to pull only those records that pertain to the specific year I am currently working with? The pricing database has a YEAR designated field for each item and the show has a designated YEAR field for each item. I thought there may be a way when I enter a record for 2009 that it only shows me the sales item records from that year? Any help would be appreciated! :

[PamRotella]

Do you know how to create another instance of the same table in the relationships graph, with a different name?

If so, how about a relationship that involves the YEAR? That's a way of showing only relevant data. There are a few ways of doing this -- the date could be generated from a calculation field that generates the current year just for this purpose, and the relationship could rely on both this field AND the ID fields you have, etc. Play with it a little & you'll come up with a model that suits your purposes.

[Kimmie]

Hi Pam,

I do know how to create another instance but how would I make that work?

[DannyDK]

You would setup your value list to "include only related values starting from" your new table occurrence in the relationship graph.

Is that the part you weren't sure how to make work or are you having trouble setting up the relationship?

[Kimmie]

Hi Danny,

I have multiple tables, contact, sales items 1 and sales items 2. I also have a separate FM DB for pricing that is linked via value list so a drop down appears in the sales item field. Once clicked, it populates in the amoount, and some other information that is needed. The pricing database has a unique number ID starting with a 2 digit year (08 or 09) to determine pricing for that year. Which table do I make an instance from? Please forgive any stupidity but this forum is my teacher! :(

[DannyDK]

I'm not sure if I understand your data structure entirely, but it sounds like you will want a new table occurrence in the relationship chart of your main database (not the pricing database) of the table from the pricing database (or whichever table is populating the drop down menu). The relationship between that table occurrence and the table you are auto-populating with the drop down menu would be based on the year.

[Kimmie]

Hi Danny,

Ok, since I already had that table occurence linked to the pricing database (separate) I added another relationship to the same table occurence for the year. Will that work or do I need to make a separate one? Also, once I do that, how would I get the data filtered to pull only by year? I wanted to be able to pull the value list for a specific year from one database (value list). Also, I am trying to build financial reports working off of that same field, year, but can't seem to do that either. See attached zip. Thank you for all your help!

[DannyDK]

It looks like you will need to create two new table occurrences of the Pricing table. One with a single relationship to Booth Sales based on year and the other related to Other Sales, also based on year. You'll probably want to give them names such as "BB Pricing Options [1 and 2]" so you remember what they are for.

Then create two value list (or duplicate the current Sales ID value list as they will be very similar to that one). Each using values from the new BB Pricing Options occurrences. Select the radio button "Include only related values starting from:" and choose the appropriate sales table.

Then, use those two value lists in the respective Booth Sales and Other Sales layouts.

As for the financial report. You will probably have to set those layouts up to show records from the sales tables rather than the contact table.

Glad to help :(

[comment]

I think I would do it this way:

YearlyPrices.fp7.zip

[Kimmie]

It looks like you will need to create two new table occurrences of the Pricing table. One with a single relationship to Booth Sales based on year and the other related to Other Sales, also based on year. You'll probably want to give them names such as "BB Pricing Options [1 and 2]" so you remember what they are for.

Then create two value list (or duplicate the current Sales ID value list as they will be very similar to that one). Each using values from the new BB Pricing Options occurrences. Select the radio button "Include only related values starting from:" and choose the appropriate sales table.

Then, use those two value lists in the respective Booth Sales and Other Sales layouts.

Hi Danny, IT WORKED!!! This is only the 5th time I edited this message! Thank you for your help. HAVE A WONDERFUL DAY!