Need to share data with web users, using ESS, not sure if I have it right
I'm new to FMP and admittedly somewhat new to DBA. I am volunteering for my non-profit org to centralize our community's contact information (currently spread across Word files w/tables & other misc. means). I've created my DB and am now ready to open it up so a few other users can help me maintain it. I've come across some questions about the way to do things as I've been learning and would love it if any of you have any guidance for me. Thanks a bunch. Primary question in bold at end of each section.
- I'd like to upload the info to our domain which has MySQL/phpMyAdmin already installed and link to it live using ESS. I've done a test run of this, exporting just 4 fields as a CSV file from the local DB, then creating a blank table in the new SQL DB with corresponding column names, matching the syntax exactly. I successfully imported the file and was able to see the data when browsing through phpMyAdmin. These were simple text fields. I have value lists which will need to be exported as well - will they translate? What are the formatting limitations, if any, when converting from FMP to MySQL?
- When I set up the column names for the fields in the blank database in MySQL prior to importing my data, I made all 4 fields a primary key rather than a unique, index or fulltext key. Do I need to make all fields that will correspond to my local DB primary keys, or only one? What is the criteria for primary key if so?
- I've created a System DNS for the SQL DB and specified it in the new ODBC data source. I am able to access the external data successfully from the local DB, but something in the relationship graph I set up is not working, because even though I edited the relationships between the matching fields in the two tables to be = to each other, with options selected to "Allow creation of records in this table via this relationship" on both sides and "Delete related records in this table when a record is deleted in the other table" on both sides, it's not updating one when I modify the other. Is this relationship limited to creation/deletion only, or can I set it up so if I make a modification to a field on one table, it can update it on the other? Do I need to somehow acknowledge the primary keys in the relationship graph?
- I chose to use ESS instead of IWP because I'm not planning on hosting the FMP file on my domain. I'm assuming that I still need to research some sort of GUI to provide my users to be able to access the info in the database from the Web in a user-friendly manner. Some will need to be able to add, modify and delete records (based on their login credentials), but all will need to be able to sort and filter the contact info based on items from the value list I've set up. Is there a way to export FMP layouts for this purpose, or is that only applicable when using IWP? If so, do you have any recommendations for GUI's to look at?
I hope this isn't too many newbie questions...I will be so grateful to anyone who can shed some light on this. I'm hoping to go "Live" with this about mid-June, so I've got a little time to become more acquainted with this process. Thank you!!