Auto-update Foreign Keys and more
I am implementing filemaker after already collecting quite a bit of data and I'd like to automate as much as possible. We are tracking online IPR violations and so far have set up multiple excel files to track various listings. For example, one file may contain violations found on and submitted to www.youtube.com. each submission will have a date, URL, IPR Number, and Violating Company. As we are now going through about 20K submissions on filemakerpro12, we need to also add a platform field that specifies youtube, facebook, ebay, etc.
I have really a few questions that lead up to my initial question. First, what is the best way to set up this database? I currently have tables for IPR owned, Unique URLs submitted, Platforms (sites where we submit violation), and Infringer Companies, each with primary keys. Then I can either have one submission table that will have foreign keys to match each of these attributes or I can break up these fields into smaller tables - do you have a reccommendation?
In any case, I would like to auto-populate the foreing key fields. I've had some success with this on the platform foreign key when I did a find for "*youtube*" and then I replaced all reccords in that find with the __pkPlatformID. I would like to create a script to do this for all the platforms that can update everything with one click. Do you think I should use a case function, an if/if else function, or a loop function?
I am now looking to populate the _fkUrlID and am having trouble. In order to get a unique list of URLs I created a new URL table and specified the field to always validate for unique values. Then I imported from my same database submissions table all the URLs (some are submitted multiple times). This gave me a list of all unique URLS which is great. then I added a table occurance for URL and matched the URL fields in this table occurance with the original URLs in the submission. Finally, I selected calculated result in the _fkUrlID and selected URL::_PKUrlID. Nothing happened. I tried this same type of calculation in the 'replace field content' tab with all records showing. It seemed like it was doing something but again, nothing. Is there another way that would work better to match up entries containing identical URLs or do you think I have an error somethere? I am really new to filemaker and database/computers in general and am trying to get over a pretty steep learning curve and could use any advice you have. thanks!!