Unique value within parent record

[madman411]

Hi all

I am further developing a solution I created a couple years ago that managed a project - in particular entertainment equipment rentals. Now I'm modifying the database to track multiple projects. In particular events that handle hundreds/thousands of products and assets that are rented from elsewhere and distributed by us. Essentially, my solution tracks who gets what on a project. All items are already barcoded by the original vendor and we use their Product and Asset codes for tracking and to aide in returning equipment to the owner(s) at the end of the job. Why keep records from finished jobs? Essentially old projects I need to keep records for should anything go missing and/or to reference in projects that happen annually.

Each project/job is managed under the Projects table. Furthermore, a Product ID is created in a Products table to manage assets in an Assets table. Many products have many assets related to them. Each table is linked via the parent Project ID.

For example:

Project - Music Festival 1

Product - P001 Camera Type A (Quantity 2)

Asset - A001 Camera Type A #1

Asset - A002 Camera Type A #2

Product - P002 Lens Type A (Quantity 1)

Asset - A003 Lens Type A #1

etc.

 

I'm running into an issue now where I'm occasionally getting the same items rented once again for other projects from the same vendor. The default validation settings I originally used for Product ID and Asset ID (Require: Unique Value) are causing problems as technically they already exist in the system, however under a different Project. This no longer matters.

I need to be able to keep the data validation error alert in place so duplicate Products/Asset ID's can't be created within the same project, however this validation needs to be suppressed should the same Products/Asset ID's come around again on a different project. The only thing now that defines an already "existing" asset or product is the parent Project ID associated with that record and of course the hidden record ID (Primary Key) FM generates with each new record created.

I'm guessing FileMaker's native Validation options don't support such a structure. Any advice would be appreciated. 

[comment]

I am confused regarding the difference, if any, between product and asset.

Suppose you have a table of Projects and a table of Assets. If you want to (1) assign many assets to one project and (2) keep the history of each asset's assignments to past projects, then you must also have a join table of Assignments in-between them.

To prevent multiple assignments of the same asset to the same project, define a Text field in the Assignments table and set it to auto-enter a calculated value (replacing existing value) =

ProjectID & "|" & AssetID

and validate this field as unique.

 

P.S. In addition to validation (and not instead), there are various user interface techniques you can use to prevent double assignments, e.g. a "dwindling" value list or portal/s, so that user can choose only from assets not yet assigned to the current project.

 

[madman411]

7 minutes ago, comment said:

I am confused regarding the difference, if any, between product and asset.

Suppose you have a table of Projects and a table of Assets. If you want to (1) assign many assets to one project and (2) keep the history of each asset's assignments to past projects, then you must also have a join table of Assignments in-between them.

To prevent multiple assignments of the same asset to the same project, define a Text field in the Assignments table and set it to auto-enter a calculated value (replacing existing value) =

ProjectID & "|" & AssetID

and validate this field as unique.

 

Hi comment

I adopted the separate Product and Asset tables as I used to work for a rental facility and that is how they arranged their data.

More or less the Product table contains a parent record to the assets related to it (see example below) to simplify the quantity data. In my solution I have a Product portal and within it I can see each product and the quantity (assets) we have on hand, how many are distributed and how many we have remaining. Further more, if I open the respective Product record, I can then see each asset, the scan history and whether it's currently "in" or "out". 

Product
Canon Camera (Product Code P20021) - (Qty 3)(1 Available)

Assets
Canon Camera (Asset Code 12345) In
Canon Camera (Asset Code 67890) Out
Canon Camera (Asset Code 13579) Out

If that makes sense.

Assignment history in other projects, if any, isn't necessary, though I could see that being potentially valuable information. The only caveat with my setup is we use the other vendor's tracking/barcodes, usually a 10-15 character barcode, structure in our system to simplify the returns process ("oh hey, we are missing asset 12345 from your job" and I can then search "12345" and see who on our team had it last). "Canon Camera (Asset Code 12345)" might appear again in another project we do next year if we use the same vendor. It would be re-entered as a "new" asset for that project despite the fact it was used by us previously the year before and exists in our system.

Your method makes sense and I'll give it a go.