7 Replies Latest reply on Jan 30, 2014 10:32 AM by philmodjunk

    New to FileMakerPro12. Help with portals/relationships

    MeredithDanberg-Ficarelli

      Title

      New to FileMakerPro12. Help with portals/relationships

      Post

           Hello FileMaker community,

           I'm hoping that someone can help out here. I'm entirely new to database creation. I've started tackling the "FileMakerPro12 Missing Manual," and have a basic understanding of formatting and relationships. Once I finalize the database format, I will be importing lots of information from Excel.

           I understand how imports work, and I learned from trial and error (before starting the Missing Manual) that it is much easier when the fields in the database match up with columns in the Excel files to be imported.

           My non-profit provides free sustainability consulting services for small businesses. The database needs to do the following:

           1. Organize business contact information (TABLE: BusinessInfo)

           2. Organize daily/weekly canvassing visits to different businesses (TABLE: EcoBizVisits)

           3. Record sustainability assessments (TABLE: EcoBizAssessments) [Note: I plan to import assessment files from Excel - one file per assessment, one assessment per business. Assessments from the past all exist as googledocs, it's a disaster, and I'm trying to whip things into shape].

           4. Record energy assessments (TABLE: EnergyAssessments) [Note: These assessments are performed by an external contractor and I would like to have the information in the database simply for simplicity of reporting. All info is in Excel format].

           5. Serve as a place to organize information about my interns (TABLE: EcoBizReps).

           6. Hold information from surveys of previously-assessed businesses (TABLE: CheckIns).

           7. Record the hours of all interns/EcoBizReps (TABLE: Timesheet).

           Per the teachings of the Missing Manual, I created self-populating serial numbers in every table, and linked tables to one another using these Primary and Foreign Keys. See my current database Relationships in the attached image.

           The first table, "BusinessInfo" will have more than 2,000 records, each record will be a different business. The second table, "EcoBizVisits," will be the one that I update most often - each day that an intern is out in the field, they will report back to me with information about the businesses that they visited. So, I know that there is a many-to-one relationship with these two tables, where there can be many visits for each business, but each visit links to only one business. I think I have created the relationships correctly, but I will attach a screenshot of the relationship as well.

           Where I am having trouble is figuring out how to use Portals to my advantage. I have a portal set up in the BusinessInfo table to import notes about visits to businesses from the EcoBizVisits table. I don't know how to set things up so that the most recent visit to a particular business populates in the correct record in the BusinessInfo table.

           I've tried to use common sense in creating fields. For example, in all tables that list the name of a business, using the "Insert Field" tool, I inserted the "Business Name" field from the original BusinessInfo table, rather than creating a new an unrelated field.

           I'll attach a few more screenshots of the three tables that I will use most. Some of the fields that I need to be linked between tables are "Business Name, "Status," "Rating," "Date of Last Visit," "EcoBizRep," and "Details of Last Visit."

           I appreciate any help!

           Thanks,

           Meredith

      DatabaseRelationships.jpg