record number calculations within a series

[markga]

Situation: I have a database that tracks materials. Each material is identified by an item number. There are some materials that have more than one vendor or price. I need to be able to assign a record number to identify which vendor and price I want to use. For example:

ID 48 has 2 records.

Record 1 has Vendor A with Price A

Record 2 has Vendor B with Price A

How do I assign the number 1 and number 2 (and so on) to each record for Id 48 so that I have

Id 48 record 1

and

Id 48 record 2

Thanks!

[Kurt Knippel]

quote:

---

Originally posted by markga:

Situation: I have a database that tracks materials. Each material is identified by an item number. There are some materials that have more than one vendor or price. I need to be able to assign a record number to identify which vendor and price I want to use. For example:

ID 48 has 2 records.

Record 1 has Vendor A with Price A

Record 2 has Vendor B with Price A

How do I assign the number 1 and number 2 (and so on) to each record for Id 48 so that I have

Id 48 record 1

and

Id 48 record 2

---

You should do the following: Assuming that you have a Materials database and a Vendor Database, create a Mat_Ven_Items database. This database has a record for each Vendor-Material combination and contains information such as VendorID, MaterialID and other information specific to this combination such as price.

You can try creating a compound key of something like VendorID & "-" & MaterialID, but you need to be really careful on how you use this to insure that you have all unique values. It is also problematic if a particular vendor carries multiples variations of the same material.

------------------

=-=-=-=-=-=-=-=-=-=-=-=-=

Kurt Knippel

Senior Filemaker Developer

http://www.database-resources.com

mailto:[email protected]

=-=-=-=-=-=-=-=-=-=-=-=-=

[LiveOak]

It sounds like you need to have two databases. The first would be "Items" and the second "Sources". Relate Items to Sources using the ID number. Set the relationship to "allow creation of related records" Display Sources in the Items database by creating a portal. Clicking on the next empty line in the portal will create another entry in Sources. Create a field in Sources called "Priority". You can use this field to assign a priority order to your sources for an item. The relationship can be set so the portal is sorted in ascending order of priority.

-bd

Sorry, Kurt. Just a case of simultaneous answer submission!

[This message has been edited by LiveOak (edited September 06, 2000).]

[markga]

Thanks folks, got it :-)