Different Approach on Historic Data, your opinion...

[ejpvi]

Filemaker Geniuses out there... I hope to pick your brains.

I was wondering if I could get some feedback on how I am building a portion of this database. But I am running into a bunch of little issues with how the end-user interacts with it.

I have a "Product Table" that is linked via relationship to a "Historic Information Table".

The relationship is based on two fields. The "Product ID" of the product, and a global field that the user can select a "Book ID"

The users like to view the product database in Table View (I know, I know... already leading to issues with this). I basically had to recreate a spreadsheet they used to use, but I had to make it interactive so they could choose what Book they want to see Historic Data from.

So in the Table view, all of the normal variables are fine, and at the end of the table, I have included a few fields from the Related Table.

Mind you.. I understand that in Table view, it only returns the first result with a related field, but there should be only a One-to-one relationship since they are providing Book ID and Product ID.. even though the historic data has tons of information with the same product ID listed many times, there will only ever be one record for Product ID and Book ID together. I hope that makes sense.

This works for the most part. In the header of the Table view, I let them select which book they want with a global field dropdown, and the screen refreshes the Related Field with the correct information.

Up to this point, it all functions correctly and exactly how I would expect it to.

Here is where the problems start...

The "Find Matching records" doesn't provide accurate results when they try to use it on the Related Fields. I had to write a script just to make basic filtering possible, but it seems they have many ways of wanting to sort, so I am thinking I will have to create some sort of layout, from the historic data perspective, that then returns to the main page via "go to related records".

Before I do this, have I made a big mistake by housing my historic information like this?

Each record is pretty simple in the Historic Data Table.

Product ID, Book ID, Status, Sell Price, etc...

So as long as you provide the Product ID, and Book ID, you can see any of the information in that record.

What would be a good practice on dealing with this, if users insist on using Table view?

Thanks! Sorry for the long post.

The main issues, seem to be 3 things:

1. Performing a Find, or Find Matching records with the Referenced information, basically doesn't work on those historic fields.

2. If I don't have information for one of the products...basically a record in the historic table with only Product ID and Book ID, I can't do a mass replace. I would really hate to have a bunch of empty cells taking up room in the database.

3. How can I have my users import information into the table based on Product ID.. and they want to update information in the references table as well.