I started working with FileMaker a while ago and so far it's been pretty fun and fairly easy to learn because I worked with MySQL in the past. However, there are still a few questions I have. I'm used to having complete freedom when it comes to databases, because in Python or C++ for instance I would simply write my own queries and get whatever data I need, no matter if a database is related to another or not. Anyway, the flexibility is still what causes me some problems. I have the following problem.
I'm working on an ERP (Enterprise Resource Planning) software for the owner of several restaurants. I need to have the following tables to start with:
Restaurants Employees Jobs Visa Health Insurances Meetings Now, the person using the solution needs to be able to create as many restaurants, employees, jobs and meetings as they want and assign them to each other. For instance, Restaurant A is going to have 5 employees, Employee A, B, C, D, E and so on, and all these employees will have one or multiple (this is really important) jobs assigned to them. This process has to be flexible, so I can assign an employee to whichever restaurant and job I choose. You also need to be able to assign one or more visa to a job (and the assigned employee), the same goes for one or more health insurances. The next problem is about meetings. Each restaurant needs to be able to have meetings every once in while, which need the ability to keep track of employees that attend the meeting. So you should be able to select employees form a portal or card window (in FM16 for instance) and they will be added to another table MeetingAttendees or something similar. Again, this needs to be flexible so every restaurant can create meetings and check which employee is present. (I need to be able to create a layout to display the meetings and who has been present, if possible also to see who wasn't).
Normally I would simply take the IDs and create tables that join these iDs together, however I'm not able to recreate this in FileMaker, if one relationship works, the other breaks for some reason. If anyone could give me a simple layout of what my database needs to look like, or if possible and not too much to ask an example in FileMaker. Your help is much appreciated.
Thanks in advance,
I would really appreciate some input on this, as I think I've got lost along the way trying to figure this out and whilst I have a working solution it seems somewhat convoluted (and is a bit sluggish to update)...
I needed to create a report from the context of contacts so that this can display sub-summary data (sorted by contact::area and contact::category) of related survey data for each contact from the single most recent survey record (within a specified report date range) as well as count how many survey events took place in that date range. Because of the context, I concluded – perhaps wrongly – that I couldn't simply put the required summary fields in the surveys table and instead needed to pull the appropriate survey data for each contact into the parent record, then add the summary fields in that table.
To do this I've added a bunch of un-stored calculation fields, namely:
1) wasOpen – since I'm working with all contacts records, this evaluates whether the contact was added after the reportEnd date or closed before the reportStart data and should therefore be included (1) or excluded (0)
2) mostRecentSurvey – calculated using ExecuteSQL( "SELECT ''||surveyDate FROM data JOIN contacts ON fk_contactID = contact_ID WHERE fk_contactID=? AND surveyDate<=? ORDER by surveyDate DESC FETCH FIRST ROW ONLY" ; "¶" ; "" ; contacts::contact_ID ; globals::reportEnd ) and used to create a relationship to a single survey record – contactID = fk_contactID AND mostRecentSurvey = surveys::surveyDate
3) survey_Q1 – answer to first question in related survey multiplied by the value of wasOpen so will only have a value if the contact is being included in the report
4) surveys_carried_out – calculated using ExecuteSQL( "SELECT COUNT surveyDate FROM data JOIN contacts ON fk_contactID = contact_ID WHERE fk_contactID=? AND surveyDate>=? AND surveyDate<=?" ; "¶" ; "" ; contacts::contact_ID ; globals::reportStart ; globals::reportEnd ) as this needs a figure for each contact whether it is included or excluded from the report.
5) summary field, total of survey_Q1
6) summary field, total of surveys_carried_out
etc (There are obviously other survey questions/dates being pulled in to the parent and summarised in the same way)
So when I set the global reportStart/reportEnd dates, goto the report layout, find all contacts and sort by contacts::area and contacts::category I get the required sub-summarised results, but I just feel I'm missing something and there is perhaps a better, more efficient approach...
Hi. I'm using FMPro 12 Advanced.
Creating a database for our community centre and I need to add a membership number to contacts - but not all of them.
I've created a separate table for Membership numbers (as not all contacts will have one, so serial numbers aren't appropriate).
Each membership has a number of details fields.
It's probably a REALLY logical and stupid problem I'm batting against - but I need to:
Select a contact>go to their details>add a membership number (go to a floating window layout, enter the details to the serial-number-derived membership) and have the details including the number show up in a tab on the contact's details layout.
I've created a relationship between my contacts file and my membership file via the Contact_ID>Contact_ID_fk and have played around with other relationships.
I've also created a simple script from a button on the Contacts membership tab to go to the Membership Layout window and create a new record. But it keeps creating a new record in the Contacts, not Membership - even though the relationship only has to create new records in the memberships side.
I'm now running around in circles - please can anyone help me out here?????
Hello guys! I wish to everyone a happy new year.
I am new to FM. I'm trying to create a Diary. I have the following tables:
Diary --< StudyLog >-- StudyPart >-- StudyContent
In my diary Layout, I have a portal. Through this portal I want to:
Create new records to StudyLog, StudyPart, StudyContent IF there are existing records to StudyPart or StudyContent I want to keep those the same! Example: I create Day: "1/1/17". Through the Day's portal, I create
StudyLog: "Log #: 1, Time: 9:00, Duration: 1hr"
StudyPart: "StudyPart: Chapter 1, Chapter Title: Coulomb's Law"
StudyContent: "Title: Quantum Physics, Author: ..., Type: Book"
In the same Day, through the portal I want to create:
When I write the StudyPart and it exists, I want it to auto complete. Same for the StudyContent.
I tried to make it as simple and compact as possible. If you need pictures or anything else, please tell me. I would appreciate it if anyone could help.
Thank you so much!
Again, happy new year!