Relationships

Hi all! I'm stuck. Really stuck. I can't wrap my head around this problem. So here's the issue: I have 4 tables: Users Projects Earnings Hours A member logs hours worked into a project as line items. Earnings are also logged per project as line items with date and amount. Now I need a report that would show total earnings per user per month. I know I'm missing something but I don't know what. Please help! Thank you! Jure projects-earnings.fmp12

Hi all, I'm new! What a wonderful forum! This is my first post.  I am running a social club that is a test ground for chefs in a number of restaurants in my area that want to have choices for celiacs (that are gluten free), pescetarians, vegans, etc. and some with food allergies. We have small groups of people come for dinners and a participating chefs cook test meals to receive feedback for the restaurant they work at.  The primary purpose is the social aspect but more so to help those of us that cannot tolerate gluten, or have life choices that donâ��t include meats, fishes, cheeses, etc. We sign chefs up from various restaurants and they volunteer their time to…

Hello All, I have a problem with my relationship. In my Dashboard I have an alert that will tell me how many materials are expiring & to be retested but my problem is it showing me a "?" The relationship graph is below Dashboard Fields Constant = 1 Constant Today = Get (CurrentDate) Global Number = Number field Value is 1 Material_Expiring Today Constant = 1 Material Expiry Date = Date Field Material Stock = Calculation (Material Quantity Received - Sum ( Material Transactions::Quantity Dispensed ) If I change my Material Stock into a Number Field It will show my de…

Hi guys! I made a database for a university. I have the following relationship Student --< Enrollment >--- Program ---- Dates Student table has the students info. The enrollment table has the program the student has done, what grade he got and the date he did that program. Here is how it looks like: Enrollment table Name StudentID Program ProgramID FinalGrade Dates 1) John Smith 10000 Calculus 1 calc-01 4.8 01/01/2017 - 01/04/2017 2) Bob Doe 10001 Calculus 1 …

Trying to start a new project, I had several different tries at my inital file. Somehow I messed up and the seemingly simple file has a glitch. The file (attached) has two tables, and they appear to properly related, but when I try to place fields into a layout they appear to insert OK. Hoever, in Browse view. I see "Table Missing" instead of the merge field I was expecting. Any help is greatly appreciated. NEW DB.fp7.zip

Good evening, I am looking for some help please - is anybody able to give me a clue as to how I can achieve what I need quicker than what I'm already doing? Description is: Two tables and their fields. CLIENT CLIENT_ID HIRE_STATUS HIRE CLIENT_ID HIRE_OUT HIRE_RETURN They are linked together via a CLIENT_ID field. It's a one-to-many relationship. Any given client can hire a widget. The date they hire it out is HIRE_OUT, and the date it is returned is HIRE_RETURN They may hire it for one day, or they might hire it for several days. They may have hired it a few days ago and as it's n…

I am attempting to help my neighborhood organization with some land issues. I am using FM 11 on a Mac running OS Sierra. My primary database is Neighborhood and the related database is Parcel. Neighborhood and Parcel are joined by the field Parcel #. County parcel numbers are the ultimate identity of property, so each property parcel is an individual record in the Neighborhood table. Using the unique parcel number I am able to align the information from the two tables. Neighborhood is of my own creation and Parcel is provided by the county and since this is a major metropolitan area, it is quite large. Parcel has only one field I am interested in, ParcelArea., t…

Hi I have a DB where I have a string of Relationships with tables as follows: Room Type>>Room Name>>Equipment in room. There will always be a Room type and always at least one Room Name but not always a piece of Equipment in the room. The tidiest way I have found to report this is from the Equipment table and use field sliding to remove potentially large areas of blank as Equipment detail varies significantly can be very detailed and may include photographs and special additional detail and notes, but without a piece of equipment in a room the reporting misses that room (and room type). I am currently resorting to using a dummy equipment record. …

Hello forum. After upgrading (finally) to FM Adv 16, I've started a rebuild of my database. The current one has been in use for about 4 years, works really well for me (single user). I had a few things that weren't quite right in my mind, so I'd like to fix them with this rebuild. This is a database for a heating oil delivery/commercial fueling/HVAC business. The relationship question is about how to handle different types of customers, and their different attributes. Current design has all customers in one table, and therefore attributes that apply to one type don't apply to the other type. I figure it's not best to have 30+ fields in a table that apply …

Hello all I'm building on the projects starter solution for my company. It is to manage client records and projects, obviously. We do university/school appliations for them, and also visa applications and various other bits of work. We call staff members caseworkers, and I know I should probably choose one or the other in terms of organising the database, but I didn't. Each client is assigned to a caseworker who is then in overall responsibility of that client, but when we start a project for a client, the project may be assigned to a different caseworker (eg, one caseworker does the client's university application, while someone else does their visa a…

Hey, I'm fairly new to Filemaker and have a few question which I hope you can clarify. I have a Filemaker solution which I use for multiple customers. This solution exist of 2 files, a layout file and a database file. Everytime a new customer comes I copy the 2 files and clear the database so they can start fresh. The problem i have with this method is that when I change something in one solution, I have to change it in all of them one by one. This takes way to many time. My question is : What is the best approach for this ? Is it possible to split up all "modules" to separate file? So I have a module (separate fm file) called Invoices, Contacts , Inv…

Hello everyone, This is my first time asking for help on a board, so please be patient! I am self taught in Filemaker so far, creating field data collection solutions for use in Filemaker Go on ios. I am currently creating a new database and data collection form. Both are still in the early stages of development, and I am open to going about this in an entirely different way. Let's get started. I have two tables: One is the "People Management (PM)" and one is the Activity Management (AM)". In PM, each row is an individual who is tied to the organization, either as a volunteer, a member, donor, etc. These individuals can attend events, and we would li…

Hi I have a table with a 'lesson-ID' that looks like 1.1, 1.2, 3.2 etc, these are subcategories of field 'type'. Each ID also jas a name to it. For instance 1.1 has is named ONE and 1.2 is named TWO. I have an exams table that looks up values from the ID table using a global (type A, B or C) plus the subcat (1.1, 1.2 etc). I need to look up values from the ID table using the type and ID -> A 1.1 / A 1.2 The fields are of the same kind in both tables, type is test and ID is number. My issue is that when I do a match it sees the '1.1' as '11'. It will match up to both 1.1 AND 11 it looks like because it refuses to show me the…

I don't understand why this is so complicated. I have a table of images. I want to show one image and compare it to all the other images in the table to be sure it's not a duplicate. So when I have the record I want to compare, I set the serial ID of that record into a global field and then create a container that should show only images where the global field matches the serial ID. Apparently I'm missing something. Your help would be greatly appreciated. Compare Photos Within Single Table.fmp12

Hello, I have a 'data retention' issue that has been driving me absolutely insane over the past two days. It is actually a multi-part issue, but rectifying the issue in this post may solve the others (so I'll keep it simple... for now). In an nutshell, I have 3 tables related by a locationID. Location locationID Equipment equipmentID serialnumber fk_locationID FLIGHT flightID fk_locationID fk_equipmentID The FLIGHT table is the main data storage entity; Location and Equipment tables just provide data to fill FLIGHT table. While Equipment stores the current location of a piece of equipment, FLIGHT also stores the location of the current flight (the flight's origin). …

HI Folks, I'm filtering portals but am struggling when it come to filtering by a calculated list field. My usual technique is to do by relationship, using great or equal to gSearchField, which usually works fine. EXCEPT when I use a calculated list field; which doesn't work using this technique with a relationship or with a portal sort. I thought patterncount might be a snazzy option for portal filtering, and although it works nicely with standard text fields, it doesn't work with with this calculated list field either. I have tried removing the line breaks with substitute, but that didn't help. I assume I missing something; any ideas please? I h…

There are three tables: Services ServiceTransactions ServiceTransactionLineItems Relationships: Services:materialName::ServiceTransactionsLineItems:Material_FK and Services:MaterialYear::ServiceTransactionLineitems:TransactionLineItemYear (Services may change from year to year, and within one transaction you might line items for both current and future years.) ServiceTransaction:TransactionNumber::ServiceTransactionLineItems:TransactionFK :Services:MaterialBudgetAllocation is a user entered number. What I am hoping to do is: Get the yearly total amounts of each material (Material_FK) and subtract that from Services:MaterialBudgetAllocation t…

Table A: StartingNumber EndingNumber Active Table B: NumberOne: Calc=1 CalcNumber Count I created a relationship TableA::Active to TableB:NumberOne, but I am struggling to finish. The goal is to get a Count of all Active people matching the CalcNumber - So each record where the CalcNumber >= StartingNumber and CalcNumber <= EndingNumber. The screenshot show the issue

I am attempting to create an inspection sheet for our Quality Lab. Right now, all parts will be on revision 1. However, once this is up and running, these parts will receive revisions. What will ensue is a table with multiple identical part numbers, but different rev levels. What I want to happen, is that whenever an employee makes a new inspection sheet, FileMaker will only request the data from the newest rev. My proposed solution is to have another field that will be 0 if it's not the newest, and 1 if it is the newest. Then the inspection sheet will only request data from records that have a 1. I have not yet figured out how to do this though. If someone else…

In my table Contacts I have two fields: JustOne: Calculation field with value of 1 - global, result is number Enrolled: Case(Current Student=1;1;"Pre-Student"=1;1;0), result is number I created a Self-Join between these two fields: Enrolled Students. I may not be creating the layout properly. I simply created a new layout using records from Enrolled Students, but all records are showing, not just Enrolled=1. I seem to be missing a step

I have two tables, A & B, with similar fields. I want to write a script that will search table B for records that match the fields that are entered in table A. I am not sure how to do this. Any suggestions?

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 detail…

How would I make one table meet four categories in my relationship. I am doing a choral music database for our school. So I have the following: Mixed, Men, Women, Small Ensemble. Each of these libraries have the same information such as; Title, composer, Arranger, Lyricist, Voicing, etc..... So when I person clicks on Mixed they get all the information categories that they would get if they picked Men, just different information in them. So would I just make four tables of the categories and have each one have a Primary key, then make on table with all the information fields and have a PK, and then each of the categries would have the FK? Thank you.

I can't figure this out so I thought I'd see if anyone could shed some light. I have a pricing database solution that includes 4 tables relevant to this issue. The Customer table contains all of the customer records. There is a record for each customer's location in this table (called a SHIPTO). Every customer will have a customer number and a SHIPTO code that identifies the particular location. There is an inventory table that list all of our inventory. Each record there is uniquely identified by an ITEMNUMBER. There is a pricing table where a custom price can be specified for that customer. Pricing can vary by location. The pricing table is a SQL tab…

Hello, I've a difficult problem. I have serval linked bases BASE 1 = Planning (red) intermediate base Planning_Objects (dark green) BASE 2 = Objects (blue) intermediate base Object_Contacts (pale green) BASE 3 = Contacts (mauve) The 3 base are linked together as follow: BASE1 ---> intermediate base (relation = id_planning <> id_objet) ---> BASE2 ---> intermediate base (relation= id_object <> id_contact) ---> BASE3 For some reason i need a field (or to create a record) that unique between base 1 and base 2. One date = one contact. How could i do this without changing my…

Sorry I am a novice and I am really hoping someone can help please! I have attached a screen shot We have a timesheet on our database, I need to create another field/script/no idea? My Manager wants it that when a specific task type is entered, another field comes up with keywords that relate to each different task type. For instance: Someone adds Strategy as a task type, another field will open with the options Scope; Estimate; Wireframe Then on the next line they choose Implement as a task type, the other field will then open with the options Technical build; Upload; Layout; Amends; Sign off I can't work out how the field will change dependant on t…

Hello Everyone, I am completely new to FileMaker Pro, but have experience working with other database platforms. I am attempting to create an app that will allow users to choose a store location from a dropdown list, and then have FileMaker populate a list of attached PDFs. I am not quite sure how to get this to work. The location dropdown pulls from the tblLocations, but I am not sure how to get the potion below to pull the proper forms in tblForms. Any ideas? Here is my table setup:

I'm stuck, and I apologize if this is the wrong forum but I'm not exactly sure where this post should go. :S ) I had a nifty way of viewing enabled records* and being able to sort them a portal Dates, Actions, and Companies or records using a virtual list using ExecuteSQL commands in a script. But, now that runtimes are going bye-bye, I have re-code my solution from scratch to work in FileMaker Go...which doesn't support Execute SQL. I'm sure there's a native workaround in FileMaker to accomplish the same thing but after searching high and low on the Web, I can't find an example file that would solve my problem. A little background: The JOB table, where the…

I am trying to set up a database to calculate and report exhaust emissions from diesel engines and am stumped at Step #1: The infamous Entity Relationship Diagram! I am looking at a few thousand engines and need to calculate how much of what kind of pollutants they kick out into the air. I have spreadsheets that list the Year of Manufacture, the horsepower, the serial number, how long the engine ran during a given year and how much fuel it consumed during that year. I also have some spreadsheets that list what the Emission Factors, usually expressed in pounds per hour operated or pounds per gallon of fuel consumed, for each of the two dozen chemical compounds I’m …

I'm reframing a question that I posted in a confusing way. Using variables seems so simple to me, but I know I'm missing something. I'll just post exactly what I'm trying to do so you guys can tell me why it isn't working. The project is a db that will handle shift schedules for employees. In the one-day view I'm looking at, the essential live field is a global date field called gDate_of_work. A portal is looking through to related records and returning a list of employees (from a table called staff_schedule_match), identified here by unique serials, who are scheduled to work that day. There is a second portal, located on the same layout, that is (correctly) showing …

My goal here is to write a database with tidier relationships than I've used in the past, so I'm intending to use discrete table occurrences of the same data. Here's the roadblock: In layout 2_week_schedule, I have 14 portals that display a staff schedule (two weeks' worth) and can't be edited. My goal is that a logged-in administrator can click on the date field of any one of those fourteen days and go to a layout 1_day_schedule that is displaying the same information, though with more details and one day at a time. The underlying table of each layout is the same, though the layouts are based on different TOs. I wrote this with a script trigger in the date…

Hi there! I have a huge problem that I hope that someone can help me with. I decided to make of my fields a checkbox with several options (different materials to be used in cleaning). Everytime I make a search where I only select ONE option in my check box (eg.soap) records where I did not mark soap on the fiel with the check box appear as well (because they contain the word soap somewhere else). I want only the records where I marked soap in that checkbox (even if it is not the only ingredient). It can not be a search using the omit option because sometimes I have more than one option marked in this check box. Thank you!!

So I have Invoices to invoice lineitems one to many. And say there are 190,000 lineitems but only 20 belong to this invoice. If I then use a portal of lineitems on invoices based upon match of invoiceID which I then filter on the portal so only certain records come up such as only shipping = UPS, is it filtering on the 20 lineitems or all 190,000. I have read many things but I am still confused. sorry it is impossible to see how it is being filtered so I can't find my answer. added --- i mean is the portal filtering 190,000 records or 20?

I have a table which has too many fields which contains various aspects of the product. Since the database is hosted in filemaker server and accessed via internet, I think it will cause unnecessary load since I think the whole record is downloaded even if I need just a couple of fileds in certain layout. I think it will be better to split the tables into multiple tables with relevant data. This will cause one to one relationship. Is it good or bad? If it's good to do that then is there anyway I can automatically create record in other related table whenever the record is added in the original table?

Dear Collective Wisdom, Hi, semi-novice here! I might have asked this question a couple years ago, but if I did I couldn't find it in the archives. I'm using FM Pro 12, on an iMac OS v10.12.2 aka Sierra. (I know I'm behind the times, am planning to catch up to FM Pro 15 soon. But for now, v12 it is.) So. I'm setting up a database to track expenses. My first bash at fields is shown in the attached screenshot. (I may prune out a couple of the fields near the bottom of the list.) The db currently has 3 records, to wit: "banjo maintenance", "Revere Ware copper-bottom pot", and "FileMaker Pro upgrade", each with appropriate info in some of the other fields. …

To preface, I'm a novice user. I have Filemaker Pro Advanced (14, I believe), and I'm trying to create relationships across a few tables. What I'm attempting to do is compare what was ordered from the "LB_Order_Info" table to what was produced in a table called "Alb_Incoming_Shipments_Item". For further explanation, the "LB_Order_Item" table has a portal that's related to the "Alb_LB_Order_Item" table. In LB_Order_Item, I key the week that the product was produced for, and then in the portal associated with Alb_LB_Order_Item, I type in the item number, and the amount ordered. On another set of tables, I have "Alb_Incoming_Shipments," where I key in the information f…

Hi guys, Sorry I do not know the terminology of what I need. I am trying to setup a database to record some info on postcodes. What I need to do is, each supplier can have 4 types of lead (back data, profile, survey, 2nd use). I have two tables for suppliers and postcodes already. I also have 2 join tables for many to many relationships. I need each field in the postcodes database to be able to store a count for each supplier's lead type. Eg: Supplier - Lead Type - Postcode - Count Supplier A - Back Data - AB - 20 - BB - 155 - CB …

Hi I'm making a little message system with filemaker 15. i have 4 tables: - Users - messages - ricipients - join table to put together the above tables. ricipient table is hold 22 records that are the 22 departements in my office. In the join table there are those fields: id_ricipient_fk, id_message_fk, date and status. the message table have a field that pick the sender account name 1 - what is the best method to set the "read" status in this table when the user open a message? 2 - how to count "unread" messages for every departements? p.s. i use a multi data files structure and an interface file. t…

Hi I have been struggling for days with this. I am trying to stop using Word to produce quotes and contracts and enter the data once in filemaker then I can produce everything with a click of a button and not have to reenter things over again. I am sure that the problem is my understanding of relationships but the relationship seems to work ok until I attempt to build a subsummary report I hope you can view a screen capture I make which I hope explains https://www.dropbox.com/s/wmgb08oxi8l8utq/capture-1.mp4?dl=0

HI, I have an existing field which is a lookup upon creation and used in relationships .. however the field that it looksup can change and I've now decided (after 6 years !) that I want it to reflect the value of the lookup field as it is (dynamically). what are the consequences of changing the field from a number field to a calculation field .. apart from some possible slowness loading layouts regards Cate

Hello! I have re configuring the Research Notes Starter Solution - it works quite well for what I need. But I want to create a couple of extra tabs with portals, but I'm having trouble understanding the how the current IDs are being created (using a calculation) for the TYPE MATCH FIELD in the Data table for Notes, Documents and Media? Could anyone help me understand this? Should I just build it from scratch? I'm using Version 14. Thanks for your time.

Hello, I am back to working in FMP after a multi-year hiatus, and I am rusty and am currently stuck. Hoping someone can point me in the right direction. I hope I am posting in the right topic. I am building a solution where we have a number of many-to-many relationships that are being used via portals. This is the issue that I am having that I am stumped on. In this instance I have the following db graph: Workshops <--=--> WorkshopStaff <--=--> Staff Each workshops may have multiple Staff and Staff may participate in Multiple workshops On the Workshops layout I have a portal to WorkshopsStaff In order to add Staff to the…

Hey Guys, I have what I think is a simple question... I'm creating a database that will allow me to create work orders. Each work order will display a customers information. I currently have two tables. 1. WORK ORDERS 2. CUSTOMERS My question is this... How do I display the fields from "CUSTOMERS" on the "WORK ORDERS" layout? I would ultimately like to be able to type in a customers name on a work order and have all of their information show up. Do I need to create the same "Customer" fields in the WORK ORDERS table? Or is there another way to display them?

Hello I am sure the answer to my question is out there, but because it is so general, I have found a lot of unrelated stuff. I have three tables as seen in the attached. I am working in a layout based on Job Sheet Main and have a portal displaying Job Roles. I wish to select an organisation (company) by name (not ID) in this portal and have the CompanyID auto enter. Which is the simplest way to do this please? Thanks

Good evening, I'm sure this is basic, but I'm just getting started. I have a table (I'll call this Master) that contains our customer list with a main billing address for each account. Each of these also has unique ID field in the table. Each our customers may have multiple locations, so I've created another table to contain the site names, address, etc. each of those records also has a unique ID that is linked to the unique ID in the "Master" table. I've created a portal view on the on the Master record layout that will show each site associated with that Master ID from the other table. This is working. I'd like to be able to click on each record in the portal …

I have a database for football inventory. I need to print out receipt for what the student did not turn in. First I want to make sure that I have my relationship set up correctly because I cannot seem to get the receipt to work properly. So here is what I have: Table: Student StudentID (Primary Key) LastName FirstName Grade Table: Inventory StudentID (Linked with Students StudentID) Shirt Short Helmet Shoulder Pads Practice Jersey Practice Pants Knee Pads Belt Table: Receipt StudentID (Linked with Inventory) LastName FirstName Shirt Short Helmet Shoulder Pads Practice Jersey Practice Pants Knee Pads Belt

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: ..., T…

Hello everyone, i am new to filemaker. i am now working on student database. The following is the problem i face. The relationship of my database is shown as follows student >> Class >> Class Line Item(portal) , student>>test>>test history(portal) so in the class layout user could see details of a class. In the test layout, user could find out the test history of a student. My client now would like to have a report based on the class line item showing the highest score of the student. But my original idea is to separate the table clearly as shown above. i try to make a portal based on class line it…

My database has only three tables... ITEMS - Which is an inventory of tools for the company. • BOOKED - Which records an audit of each time each item is booked in or out of the stores • TESTED - Which records an audit of each time an electrical tool is PAT tested [safe to use] The individual Items are displayed in a list layout called 'Item List' which is basically the 'Home Page'. The BOOKED events are displayed in a portal in a 'form layout' called 'Item Booking'. I've set the sort order in the portal to show the last 'booking' at the top. The easiest way to this portal is to click the small blue "go" icon in record on the "Item Bookings…

Hello I am trying to relate multiply records in the same table. I have a table called Jobs with each record having a unique serial id as well as a job number. Often a new jobs comes in that is a revision of a previous job (or jobs) that has been billed and closed. My database users would like to link the new jobs to the old ones. I have created a second instance called Jobs_related and also tried putting a join table in between, but nothing I do to join them works. Thanks

I'm having trouble understanding how to set up these relationships. I have the following tables: Companies, contacts, notes. There can be many contacts for one company. There can be many notes for each contact. There can be many notes for each company. When I pull up a company record I need to see all the notes for that company including those which are for all contacts from that company. So even though a note is attached to a contacts record it should also automatically be attached to the company that contact belongs to as well. What's the best way to set that up? Thanks!

(I'm using Filemaker Advanced 15 on Mac OS X El Capitan) I have some filtered portals that I need to turn into portals that are filtered by relationship. I can not figure out how to set this up properly. I'm trying make a portal that will show me a list of all of the photos that still need to be Touched-Up. When a photo is selected for touchup the customer record is linked with the Photo by a key.Customer -> PhotoAfter the photo is emailed out for touchup "EmailSent" goes into Photo::EmailedStatus and if the touchup comes back and needs to be sent out then "Rejected" goes into Photo::ApprovalStatusI tried to make a self-join relationship between Customers …

Hello everybody, I'm struggling with summary fields in a related portal. My situation is as followed: I have an Article table, a Packaging_join table, a Packaging table, a Stock_Entry table and a Stock_Join table. Relation: Stock_entry -< Stock_Join_Article >- Article -< Article_Join_Packaging >- Packaging This works very well, I can enter different quantities per package per article. I made summary fields in the Stock_Join_Article table to see how many article I have for each type of packaging. When I create found sets for each package I get the correct amount in stock, so this seems to works. Now I would like to create a portal …

I've driven myself more or less crazy trying to figure this out on my own. The relationships themselves are rather simple: Each project has a list of questions. Each question has a person who created the question, and someone the question is assigned to. A question also has a number of responses associated with it which each have their own "creator". There are 3 main tables to cover: Questions, People, and Responses People consists of 3 fields: The UID, the person's name, and the person's email address Questions has 2 fields which relate to People: creatorID and assigneeID Responses has 1 field which relates to People: creatorID. The way the r…

I have a solution with 4 Databases. There is the main-data-database on a Filemaker Server (only data, no relationships in the Table-Occurences (TOs)) and a GUI Database on the Desktop (only Layouts, TO's are included from the Server-Database and on this TO's the relationships are defined, so I can use them in the Layouts. Than I have a 3rd database, which is a subset of the server database and lies on an iPad. This 2 data-databases will be synchronised by MirrorSync. And the 4th Database is a special GUI Database on the iPad which uses the 3rd database for Data. The iPad GUI Database also holds TO's with relationships. Now, I need a custom privilege set to forbid cha…

Hi Everyone, This problem has probably been asked a million times but I cannot work out how to achieve the following: I have a table called UserDocuments2 and in this table I have 2 fields (among others) called UserDocuments2::DocumentName and UserDocuments2::DocumentText This works fine and I can create new documents and store in a portal. What I want to have happen is over on my Notices Table, when a user selects the field Notices::Header a DDL using the data from UserDocuments2::DocumentName is entered into Notices::Header (this works) but also the associated data from UserDocuments2::DocumentText is auto entered into the field Notices::…

I have a database with each record representing one account. Within each account record, there are ten different companies the account may have been sent to (one individual field for each company within the same table), along with the results of that effort. Both of these make up the company data set. Also within that single record there is an identifying ID Code, which represents the type of account it is. What I want to do now is create a table with a portal where I can select or enter an ID Code, and get each instance of that ID Code with each individual company data set. So if with the individual account record I noted that I sent it to four of the ten com…

I have a FM solution for the haulage company I work for. Each job is booked into a "Loads" table, and the price and any additional charges for each load is created in a "Charges" table (although charges can also be created that are not related to Loads e.g. storage charges). When ready to be invoiced a new record is created in the "Invoices" table and the invoice is allocated to a particular company. At which point all available charges for that company are displayed and can be pulled onto the invoice (by simply adding the invoice's serial number to the charge selected). I'm having some issues, however, managing payments. At the moment any payment is logged in a…

I'm pretty new to filemaker and databases in general, and are strugglig with the following problem. I got a table for registering damage done to art prints. There is one table for a complete edition of a print (table 1), with a child table for every single instance of the print (table 2). For instance "Title" (table 1) and "52 of 250 (table 2). The table for damages is done as a child of "table 2". When entering information in table 3, it would be great to choose the name of the print from table 1 (the grand parent) first as a drop-down list, and then only get the related options in a new drop-down list from table 2 (the parent). There is probably a pretty simple solution…

Hello folks! Setup: Our graph looks like this: People >> FamilyMember >> People. The People table has a name field and a primary kp_PeopleID field. The FamilyMember table has but two fields: kf_PeopleID and kf_FamilyMemberID. The first relationship predicates are: People::kp_PeopleID = FamilyMember::kf_PeopleID. The second relationship predicates are: FamilyMember::kf_FamilyMamberID = People::kp_PeopleID. We have a portal on People layout, that looks at FamilyMember (and the second People table occurrence also, to grab their names). This is all fine and well to specify other People as Family Members, we have a scripted picker that creates related recor…

Hey, I want to create invoices with three different types of line items, 1) rental items, 2) people for hire, and 3) rental add-ons. Should I place all three in a single products table with extra child tables for each's type-specific info or should I create three different foreign keys in the line items table and create relationships to all three tables?

I have a table with NAME field containing Return Separated Values (RSV). I want to run a script that will add more RSV's to the same NAME field in the same table. This will be from the same NAME field on other rows. It will be based on a "match" on a PRODUCT field. I don't want to wipe out the values already in the NAME field obviously. Any ideas?

i made 2 tables one is drug where there is information of respective drug other is prescribing table and made following relationship i made drug field in prescription table as value from drug name of drugs but when i am selecting drug select in prescription table all fields are becoming same medicne instead i want to it to be different please someone help me drugs.fmp12

FMPA15/FMS14 I have Three tables StockTake Barcode_Serial_Number Timestamp_Of_Scan Inspection Barcode_Serial_Number Works_Order_Number Works_Order Works_Order_Number Stock_Code StockTake is a log of someone scanning unitque Barcodes and their timestamp. Inspection is the 'end of line' scannign machine when teh product is booked into stock. Works_Order is the list of Works Order Numbers and the stock code of the product. I don't undetand relationships enough; so some guidance on where i should be reading would be appreciated. I need to do a few things, but they all centre around…

I currently have a portal that lists all courses taught by an instructor (Year, Quarter, Hours, Percentage, Costs) I also have a portal within the same layout that includes summary fields (total hours taught and Average). I need another portal (or...?) within the same layout that has three fields (Year, Quarter, Hours, Total percentage for each quarter) and possibly a running total. I want to quickly see a summary of each quarter instead of having to refer to the complete list of courses and do the math. Seems easy - just not figuring it out.

Hi guys, Please help! So I have 3 tables, Student --< Enrollment >---- Course I would like to search for the students that haven't enrolled on a certain course. For example I have this course: Id Name Credtis 202 Artificial Intelligence 4 In the enrollments table I can see everyone that has taken this course, How can I search for the people that haven't taken this course?

Hi been stuck on this for days so im thinking it may not be achievable i have 2 forms 1 customers the other a buy form the customers also has a portal showing history of records from the buy form my problem is we get tired of switching to the customers layout to see peoples history what im looking for is a way to see history on the same layout same table i know i can see other tables through a portal but cant figure it out that i can see history from the same layout / table very much appreciate on a pointer in the right direction please

I have a PRODUCTS Table (1000 rows) containing a field called NAME. I also have an ATTRIBUTES Table (20,000 rows) with useful attribute info (in 3 fields e.g. F1, F2, F3) that I want in my PRODUCTS table layout (for export to Excel). I can't see an easy way to join the 2 tables. ATTRIBUTES::MOD_NAME is never exactly the same as PRODUCTS::NAME. ATTRIBUTES::MOD_NAME is a string that is sometimes partially contained in the string that is PRODUCTS::NAME. This will be true zero, one or a few times. ATTRIBUTES::MOD_NAME is contained in one or more of the PRODUCTS::NAME -- that's the "join" I want to do (based on a calculation/function e.g. StrMatch from http…

I have an FM Table called PRODUCTS (shown on lines 2-5 in the image file). I want to transform this into an FM Table called TRANSFORMED (shown on lines 7-8 in the image file). How do I do this in FM? (I am indicating return-separated lists with tilde's (~) in the image.) Also, does this TRANSFORMED table structure make sense?: I intend to have a JOIN on the TRANSFORMED table's PROD_ID field with the PROD_ID in my SALES table for an export (to Excel) of the SALES table based on a match on one of the return-separated list values in the PROD_ID field.

Hi, For my solution I have set up a party model, with separate tables for People, Organizations, and Parties. Since a Party can be either a person or an organization, the user creates a new party record, selecting whether it's a person or org; then a related child record is created in the appropriate table. My question is, I would like to come up with some sort of compound party model, but am not sure how to structure it. In my solution, Parties can own interests in leases, and they get credited with line items in a line items table. So for example I might have Bill Smith and Jean Smith with separate records in the Party table (and associated records in the Peo…

Simple situation not behaving as expected. I have a Layout with a Portal to a child table. The Portal is displaying 5 rows and there are 3 Fields displayed on each row. Record creation is allowed so that the User creates Child records by simply entering data on the freaks line. No problemo. So, the final field in the Row is set to require Validation: "Not Empty". So if I enter data in the portal and then click outside the portal, sure enough, the Validation message pops up as I set it, to ask for a value in that Field. But I find that if the User simply Tabs out of that last field into a new portal row, he can work merrily along on this new record whil…

Hi, I'm new to both building databases and Filemaker, but am trying to set up a simple database for managing transactions. Currently I have two tables, 'Company' and 'Transaction'. To avoid duplicates I have tried to store information about both buyer and seller in the Company table, i.e. one primary key referencing two foreign keys in the same table. So I've used Company_ID as the primary key in the Company table and link this to two foreign keys (Buyer_ID and Seller_ID) in the Transaction table. This is where the problem arises. I am not able to access any information in the Company table from the Buyer_ID or Seller_ID. This only works if I use only one foreign key…

Hey guys, I don't think this one is actually possible, but hopefully someone out there might have figured out a work around Lets say for example i have 3 tables in bold and their following fields in italics. Order -Order Name -Order ID Product -Product Name -Product ID Product Tags -Product Tag ID -Product Tag Name Order Details -Order ID Match Field -Product ID Match Field -Product Tag ID Match Field -Product Quantity I would like to have on an Order layout, a list of all Product::Product Name, and their Order Details::Product Quantity. Even if the product quantity is zero. What i do not want to have to do i…

I have 4 Tables of Sales Records (1 per Store) for the same 1000 products (each sold in the 4 Stores). Table1 (T1) has 30,000 records T2 has 50,000 records T3 has 10,000 records T4 has 10,000 records The fields in the 4 Tables have different names and contain similar “core” content. Tables are added to each month with additional records (imported into FM 14 Pro Advanced from Excel). Unfortunately the same Products are named slightly differently in each of the 4 tables. Each store / table has a (text) Product-ID but (for the same product) it is different per store. I do want to rectify this “naming problem” (perhaps …

Hi, i am trying to make a filemaker db for a courier business me and a friend are doing. I have a bit of knowledge on fm and just wanted some expert advise on the best way to setup the whole db , layouts and relationships. I have a friend creating the booking website with the payment module and will import to pm db on booking paid for (somehow ) I have attached the image to give an idea how i want things laid out and some layouts will be for ipad mini and some on the pc which isnt too much trouble but more the initial setup. Please if you have nothing helpful to say dont say anything at all. I appreciate any assistance. Thanks Stu

Hello Everyone; i am tyring to get a type ahead working on my system regarding point of sale. The only issue i see is taking teh user to another layout to use the following type ahead system: If ( start > WordCount ( haystack) ; 0 ; Let([ needle = LeftWords(needles; 1); word = MiddleWords(haystack; start;1) ]; Case ( Left ( word; Length(needle) ) = needle; If( WordCount(needles) < 2; 1; FindWordPartsInText(RightWords ( needles; WordCount(needles) -1); haystack ; 1) ); FindWordPartsInText(needles; haystack; start + 1) ) ) )

I am building a solution for a business that supplies 4 products. I need to have a layout that shows a portal with all the customers listed down the left and then 4 boxes across the row (one for each product) that a value is put in to show how much of each product the customer is ordering. The qty fields need to be live so that values can be updated on the run. They also need to create an invoice ( and update that invoice at each value change) for each company. I should also be able to enter a date in the search field to bring up past orders for that date and for all the listed customers. Can someone help me out with some ideas on how to do this?

This should be easy, but I cannot seem to get it: I have three tables - SalaryScale, Teacher and Courses Fields in question: SalaryScale: ScaleYear, ScaleStep, ScaleAmount Teacher Fields: Teacher, TeacherStep Courses: CourseYear, CourseTeacher, CourseHours, CourseCost The ScaleAmount changes from year to year (the ScaleStep does not) Typically the TeacherStep field does not change I created a relationship TeacherStep::SalaryStep and thus I can see the current salary associated with that step I also created a relationship Teacher::CourseTeacher and thus I can see the cost of the course through a calculation that will multiply the TeacherStep, …

Is there a difference in how Filemaker reads/acts on the two following Relationships: 1. Parent Table A --< Join Table AB >-- Parent Table B and 2. Parent Table B --< Join Table BA >-- Parent Table A ? After my last disastrous foray into designing a Relationship Graph, I went back to the Filemaker Training Series to re-re-re-read about building correct ERDs and Relationship Graphs, but only managed to confuse myself even more. The FTS examples all use the Anchor and Buoy concept, but seem to have a huge amount of unnecessary repetition, unless I'm completely off base. Using their "04_Bonsai" Relationship Graph as an example, they have one …

Hi guys! I'm using fm 14, I would really like your help with making a report in which I need to show a many to many relationship. I know how to do this easily with sql but have no idea how it's done here. Basically I have 3 tables: Student ---< Enrollment >---- Course Soo, I would want to see in a report a student and the courses he has picked and in another report each course with the students it has?? Please, would be really grateful of your help

FMP Adv 15. I’m using the file open script step to open a filemaker file (file2). File2 has a script trigger on first window open but it does not run when it opens. Is there a way to run a script in file2 when it opens?

Hello! I have a field Defaults::Store and Progility::Store. What I am trying to achieve, is a script that copies the value found in Defaults::Store and pastes in Progility::Store when a user clicks a button to run the script. I am making this work at the moment by using copy/paste functions, but would ideally like to do it without using the clipboard. I have tried several things with Lookup/Auto-Calc but haven't had any luck. The value in Defaults::Store is also a member of a value list. Thanks! Cameron

I am starting a new DB and pondering the schema. In a past project I had various company types that required their own section but I had a parent table called company which would hold basically the company name. so: company: company_name, id - agency: [ separate table, agency details ] - client: [ separate table, client details ] - vendor: [ separate table, vendor details ] I also did the same for contacts: profile: first_name, last_name, id - contact: [ separate table, contact details ] - talent: [ separate table, talent details ] The new project has similar requirements and I am wondering if I should normalize it further and merge com…

Good day. I am trying to create a multi-field lookup that will match all records to a blank field. Specifically: File A File B Field A1 = Field B1 Field A2 = Field B2 Field A3 = Field B3 All three of these are one (File A) to many (File relationships. The data enters File A in a portal. What I want to do is set this up so that, if one field in A is empty, the relationship will match all records for the corresponding field in B, and that the match would only be limited by the other fields. Ultimately, if all fields in A are empty, all records from B should come into the portal. …

Here’s my basic problem (other than my robust two-digit IQ): When I change data in a Table via a Portal, the data in an upstream Table gets changed as well, which completely messes up the entire Universe as I know it. Here are the particulars: I’m developing a solution strictly for a Desktop computer which will be used very intermittently by a max of 6 people. It will be used to track Air Emission Reduction Credits (ERCs): A company can own many ERC Certificates, but an ERC Certificate can belong to only one company. An ERC Certificate can have up to five different pollutants on it (NOx, SOx, VOC, CO, and PM10), but each of those poll…

I want to make a report like as attachment. how to group the branch code and calculate the hour & $. Thanks for your help.

The (small) company I work for has need of a database, and I have been given the responsibility to design it. In learning through trial and error with FileMaker, among the more vexing problems of mine is the exactitude required when displaying related records in a portal. My colleagues are extremely sloppy with data integrity, even with drop-down menus and autocomplete enabled. The company is a literary agency, which takes manuscripts (defined as Title) from our authors (Author Name) and sends them out to various editors at different publishing houses (Editors, House). I currently match Author Name and Title between two different databases, and based on those two factors,…

Forgive me if this has been aswered before, but couldn't find anything that answered my question. Table ANSWERS Table REMARKS In the relation definition between them, I have the ANSWERS on the left, and REMARKS on the right side, with Allow Creation and Delete Parent checked on the right side. Now the situation is that there can be multiple ANSwers with one and the same REMark. In effect, For each of the ANSwers, the same Remark will be shown. This is desired behaviour. If I delete 1 (out of ex. 3 ANSwers), the sole REMark is deleted as well. I was hoping to be able to define in the relation that the parent record be deleted when "all records are del…

I'm working on a solution currently where I am using a variation of the Selector Connector model https://www.geistinteractive.com/2014/11/21/filemaker-selector-connector-video/. In discussions I have seen there is great emphasis on the idea that the Selector or Connector tables should consist solely of global fields. My understanding is that the concern is regarding record locking. What I was wondering was how the use of any unstored calculations might impact that risk or what other things I should be aware of if I were to include unstored calculations along with global fields in those tables?

I have an accommodations/ticket order database. I would like to setup a rebooking system where a person could rebook in advance their accommodation/ticket package for the next year. I duplicated the database and named it REBOOK. The problem I am having is that if there is a change in their order in REBOOK and want to update information in the order form, it does not update when I import the changed info. Any thoughts Basically what I want to do is add an updated order. There are two related tables - KB 16 & ticketOrd. (See attached) T

I'm trying to track land ownership over time but running into trouble when I try to make the relationships meaningful. In this village I am researching, a large stretch of land may have been subdivided into many smaller fields. Some of those smaller fields may then be further subdivided, and some others may be combined into larger fields, perhaps combined with areas which were not part of the original very large field they started from. So for example in 1800 there is a field A. In 1846 this breaks into many fields: AA, AB, AC, AD ........ In 1860 AL and AM combine. In 1865 they combine with part of another originally larger field system - AL + AM + BD. This lar…

I have a layout with several fields including six fields named Code 1, Code 2, Code 3, etc. all with dropdowns that each have six choices the user can select. When the user saves their choices the current structure enters them into a table with a relationship to one of the six columns. I think it should be putting them into a single column identified by the foreign key for normalization reasons. Is that correct? Thanks.

I'm trying to figure out how to attach a filtered value list to a field in a portal. A copy of the solution is attached. I want to filter the value list attached to the fkMemberID field to display records with Member::Status = inactive. Meetings.fmp12

FM 12 Adv. I'm trying to setup a database of meetings that records attendance and voting at each meeting. I have a Meetings table and a Names table. On each meeting record I select attendee names from a value list in a Names portal. I would like to include a field to record an attendees vote (yes, no, abstain) but I can't figure out where to put it. I doesn't belong in either table. I then need to create reports that lists the attendees and tallies the voting. I was wondering if I need many-to-many relationship and how I would set it up.

Hi I am developing a food Production database. i have a table for the products and another table for the ingredients - all working great. Now i am creating a production table , the way it works there is a production to a product at a certain date. The Ingredients to the product get fed in through a portal. What i need is that the production overseer at the manufacturing plant should be able to check every ingredient of the product if is approved to there standard and is available in manufacturing plant prior to production. The approval are Production to production basis not ingredient basis, meaning on every new production i want to be able t…

I am pseudo-new to Filemaker. Currently I have a related FK field appearing on a related layout (Contacts::Contact_ID on the Task Details layout. I would like to be able to add a new task that is related to a specific contact. I particularly like the idea of having a popover window showing a list of contacts, selecting a name, and replacing the contents of Tasks::Contact_FKID with this name. The idea for this solution comes from the Invoices solution/template that ships with FMP15 Advanced (Customer Popover). I have gotten to the point where the list of All Contacts/popover appears but I can't seem to click on the name and have it added to the field in a new record o…

Hi everyone! I'm making a database for a small university in which I work in . It seems really simple to make with FM until I think about the courses and their prerequisites . I 'm not sure how to make this database having in mind this task , the rest shouldn't be a problem . Looking around the web I found a database model with exactly what I need: According to this I see that prerequisite is related to itself, how do I get this to work in filemaker pro? How can I make a course available only if the prerequisite has been coursed before?

Hello all. I have been using FM Pro for many years now, by creating simple databases for keeping my customers info, invoicing and general office stuff. I'm currently using FM Pro 14 Advanced. I 'm at a dead end now, trying to edit an existing database. What I'm trying to do is: I have a list of serial numbers of products I have received in past days. These numbers are stored in an existing value list called "Tested". I also have a field called "Unit Serial", where I punch the serial number of the incoming product. What I need is, if the entry of this field exists in the value list, to get a warning message saying that this product has been tested befor…

Hi, I got stuck with building a proper relationship scheme. I'm making an database with several tables. One table contains information on archaeological features (key field Feature ID), another contains data on documentation. One document may contain information on several features, for this reason in documentation table I've created several fields (Feature ID 1, Feature ID 2, etc). The two tables are related through Feature ID=Feature ID 1; in the second table I've made a self-relation between all Feature ID fields. In the Features layout I've made a portal displaying related records from Documentation table. Unfortunately it only displays related to the first …

Hi, I'm still in the middle of deploying a new solution for the company I work for. We are a manufacturing sub-contractor and we use filemaker for most of our systems such as orders, products and stock etc but I am rebuilding the system in a new database whilst adding new features. The bit I'm stuck on is the our Bill Of Materials (BOM) for a product contains a list of the stock codes of components that are used to build it. The stock codes are ideally created and all of the information entered and then locked so they cannot be changed since they will be on many products, but if there is a typo or a mistake then it will need to be updated. Ideally I want the BOM's to look…