How to auto-populate a field that isn't the primary key

[b2683152]

Hi,

I am trying to use Base to make a database to catalog a collection of books, audio, and video files. My database currently has 4 tables:

Tables: Books, Audio, Video, Index

The Index table has the fields “Global_ID”, “Title”, and “Category”, with the Global_ID being the primary key. This table is meant to hold a list of everything (books, audio, and video titles, as well as a unique identifier for every record). The Category field is a text field that says what type of item the record is (book, audio, video).

The Books, Audio, and Video tables each have 2 fields, (BookID, Global_ID)(AudioID, Global_ID)(VideoID, Global_ID).

How can I relate these tables such that each item has its own primary key (ID) within its own table, but all items have a unique Global_ID that is unique across all tables?

Would my data entry to add new items be done in a form based on the Index table? And How would BookID, AudioID, and VideoID be automatically generated in the corresponding tables when I create records in the Index table?

I have tried opening up the relationships window and creating linking each tables primary key to the Global_ID in the index table, as well as trying to make BookID, AudioID, and VideoID fields in the Index table and linking each tables primary key to the similarly-named field in the Index table.

Thank you.

[Ratslinger]

Your approach seems to be overly complex. I would reduce this down to one table. Unique ID, Media Type, Name (and I might add things such author, year, etc.). Media type can be a list box with a choice of ‘Book’, ‘Audio’ and ‘Video’ to help eliminate typing.

Now when you want to view in a form, you can filter the table results with Media Type and only show those entries for say ‘Video’. Or create a search on name and display what Media Types are available.

[b2683152]

Thanks for your reply. The reason I was trying to set it up this way is because I plan on also keeping track of things like number of pages for books, author, as well as runtime, rating, size, etc for video and audio. Should I put all of these in one big table?

[Ratslinger]

Well I can see it both ways. Since the size of this DB will be relatively small (not 10’s of thousands of records) one record for all should work. Going the other way ( 4 tables ) all you need to do is link the ‘Name’ from ‘Index’ table to ‘Name’ in each of the other tables. Each has a one-to-many relation (‘Index’ to other table). However, combining records from multiple tables ( IE: a search/query) will be a challenge since each has different content.