Relationships

I have been working on a movie database and found a schema that makes sense to me as far as the relationship. But one thing I am having problems with is I am importing an Excel Spreadsheet with 715 rows of data. My problem is that each Movie has four actors, two main ones and the other two character actors. I have separated them into for different columns in EXCEL. Actor_1F is the Actors first name and Actor_1L Last name, I did this for each of the four actors. I really do not think this is correct. Because when I look at the Schema I cannot figure out how to relate the four actors to a movie. What I am doing is showing the picture of each actor and giving their Bio.…

Hi all: I'm stuck on how to set up the relationships/tables connections for this scenario and need your guidance: It's for a school system where they have Districts, Schools, Staff (Name), Roles, Grades and Subjects. Where I'm stuck is, let's say a staff person is a Counselor—she can be a counselor for the a district or one or more its schools. She can also have more than one role, e.g., paraprofessional, at a school (within the same district), and each role can apply to one or more grade levels and subjects, e.g., she can be a counselor and a teacher that teaches different subjects at different grade levels. I've attached a crude map of what it is so far, …

Hello there! I am working on a database as follows: - DB structure is ParentTable => ChildTable => GrandchildTable. - ChildTable is a portal in ParentTable with 4 fields: IDMatchField, CostCode, Description and Total. - GrandchildTable is also a portal related to ParentTable via GlobalField and a SetField script trigger on RecordLoad, and it has records: PrimaryKey LineItemDescription, UM, Qty, Amount and AmountSum (a summary field). - ChildTable::Total can be entered either manually or via calculation = GrandchildTable::AmountSum, for those ChildTable records that need to be broken down in GrandChildTable. Let's say I enter my first record …

I’m responsible for a Filemaker solution that manages rental equipment. It has slowly evolved over the years, as the requirements have grown and I’ve tried to keep up. We’re beginning to hit some limitations that I’m thinking may be down to the original structure of the database, so I’m looking for some advice and input on how best to address the issues. The fundamental layout of the database is fairly simple. We have an Orders table which is related to an Items table. Users setting up an order first select the customer, and the dates for the rental. There’s a Date Out and Date In. Rental dates frequently get “nudged” when customers’ plans change …

Hello all, I could use some help "analyzing" this: I currently have a ONE table called DOCUMENT. This one table was intended to hold any IMAGES (graphic, photo, diagram, etc.) and any DOCUMENTS (spreadsheet, MP3s, manuals, etc.). I would then relate the DOCUMENT table to any other table in the database that required the storage or access to DOCUMENTS. FIRST OBSERVATION: I have noticed with a differentiation between two "classes" of documents within FileMaker options: For example: Optimize for IMAGE or INTERACTIVE CONTENT. SECOND OBSERVATION: An average User (including myself) could benefit from seeing a separated "view" of IMAGES (like p…

I can't believe how rusty I've gotten at basic filemaker setup... Here's what I'm trying to do... I create custom mixes of seed beads for sale. Table name is Mixes. These mixes might include up to 12 different seed bead colors. So I have another table with my seed beads in it called Beads. Fields are Item Number, description, color, size, quantity, cost, etc. These are manufacturer items. What I want is a layout where I can enter the Beads Item Number and have it link that bead to the Mixes record. I feel like a join table is what I'm trying to do, but for my life, I cannot remember how to do it. I have had some success with this approach with another database …

Could I please request some assistance with my relationships and fields? I’m not entirely sure if I’m designing them correctly. I’m creating a movie database. I’ve created separate tables for actors and actresses. I’ve created many-to-many relationships for them, using two separate junction tables. (I call them Junction tables. I know some people call them join tables or jump tables. Please let me know which term is more acceptable in Filemaker Pro.) The problem is this: If I enter an actor’s name more than once, it appears more than once in the Actors field. For example, in my database, the name Jack Nicholson appears numerous times, each w…

Hi all, and happy holiday season. I'm stuck how to set up the schema so I can create a report involving two child tables connected to a parent table. (I guess things around here have changed; my existing account is gone and I can't upload an image or file so I'll have to describe my request textually.) The three tables are Grant (parent), Participant (child), and Vendor (child). Grant pushes data (in a one-two-many) relationship to each child table. I want to print a report where it summarizes participants by vendor, so each vendor will have a list beneath it of related participants through Grant. How do I set that up...or can I? Cheers, …

I just recently "rediscovered" this technique, having stumbled upon it: Say you have two tables A and B. Make a global field in A, and create a relationship between the two tables based on the global field in the A being equal to some field in the B. Now set the global field in A to a return-delimited list of values. Then, from A, do "Go to related record" in B (match current record only), and boom, you get all the records in B that match any item in the list. Sure beats looping and finding. My question is: is it what people refer to as "Magic Key", or is that something different?

Hello all, I have been working on an area going on two weeks to try as hard as I can to figure out how to do what I want. I have decided to make and provide: stripped-down database file data model pdf screenshots In my previous requests for help I struggle making my "case or problem" clear and I probably frustrate those willing to help: I hope this attempt is much better. I have tried color-coding to match Entity Type to aid in understanding: Relationship Graph Layout Header Data Model PDF I have colored fields on layouts in BLUE as they are the objects that apply to the area I need help with. Screenshots …

Hi all, Hoping to get your thoughts and advice on my attempt at modeling the tables and relationships to support the following: 1. I have many different tables like PERSON, PRODUCT, FACILITY, etc. where each record will have many images. For example: A PERSON (John Doe) will have many images that depict John Doe. A PRODUCT (XYZ 500) will have many images that depict XYZ 500. A FACILITY (Wilson Convention Center) will have many images that depict Wilson Convention Center. Etc. 2. Since I need a separate table to hold the "many" images for each of the tables above - it seems like a SINGLE table that can hold ALL images would be eff…

Beginner FileMaker user here. I am working through the Philosophy of FileMaker series on Udemy and have a question about a project I'm working on. I am building a FileMaker project to track projects at the shop I work at. Sometimes we have projects that we break into smaller projects and assign different engineers to the sub-projects, but the sub-projects are always part of a larger project that has a specific number per overarching project. For example, project 584 might be Charleston Courthouse, but it gets split into 1st floor, 2nd floor etc. Sometimes we don't break the projects up and the whole project goes to one engineer. I have a Project Manager table, an En…

Hi all, I can not figure out how to do this: "One" PROJECT will have "many" different STATUSES over time. I built two tables (one table for project and one table for status in a one-to-many manner related by "project_id"). I built a status portal on project layout: this works just fine for recording history of statuses for a given project. But…how can I display ONLY the most recent status based on date on the project layout? I did build the status table with additional fields to hold the status start date AND status end date. I believe the answer lies in a calculated field in the STATUS table that somehow displays the most RECENT status based on…

Hi everyone, I'm currently working on a database to manage condominium expenses, but I've hit a roadblock and need some assistance. I've created a table that lists all the expenses, each assigned to a specific category (e.g., electricity, insurance, lift, cleaning). However, I now need to separate these expenses into individual tables for each category (electricity, insurance, lift, cleaning). Can someone guide me on how to accomplish this? I'm feeling quite stuck and would greatly appreciate any help or suggestions to resolve this issue. Thank you!

Shifting gears on summarizing related records a bit. My file has the summary page that summarizes data from a few other tables, including the transactions table. I have a portal on the summary page, that shows all the transactions for that month. Each transaction has a field showing what method of payment was used, and I have a value list with the different methods. In his case, I created a table that has the payment methods, and my value list is from that table. What I need to do is, on my summary page, have a portal that will have a row for each value list item, then a field that shows the sum of each transaction paid for with that payment method for that …

In my file, I have one table that is a list of transactions. Then, I have another table that on it's layout, summarizes things from a few other tables, including the transactions, and it has a start date field and an end date field. Those fields are simply the first of the month, and the last day of the month. In my relation between the two, it's the transaction date is greater than or equal to the start date, and less than or equal to the end date. So far all is well. On my summarize view layout, I can see the transactions for that month as I'm supposed to. Here's the problem. I want to have two fields, one of which shows the total transaction amounts from…

I'm reaching out for some help on how to model my data for a particular area of my business. The business is a printing company that makes a wide variety of items. For example: brochures, catalogs, posters, etc. These items my printing company sells have many attributes that need to be entered in order to fully describe them. Attributes such as: trim size; folded size; paper material; ink configuration; bindery operations, etc. All of these items are build-to-order for customers. A typical business/process/document flow is as follows: 1. A Customer (or prospective customer) requests a price from us and they will also provide enough information about the p…

Hi - I feel this must be addressed here somewhere, but I can not find it. 3 Tables: Invoices with fields: PrimaryKey; isLabor (Global); and others LineItems with fields: PrimaryKey; InvoiceID (ForeignKey); LineItemTypeID (ForeignKey), Quantity; Quantity(Summary-Total of Quantity); isLabor (Global); and others LineItemType with fields: PrimaryKey; isLabor; Quantity; and others The invoice layout has invoice fields, and a portal to Line Items. That is all working fine. I woud like to put a summary of the Quantity field on the invoice layout outside the portal, but have that quantity ONLY add up rows in the portal where "isLabor" in the LineIt…

Hello, I'm needing help with creating the relationships for the requirements shown below. (For each table, the user should be able to add one or more notes. Should this be a single table or should a Notes table be created for each corresponding table?) Thanks for any help with getting this started. Person 1. Person record is unique. (Note: There is no unique identifier (such as SSN) that the office that manages this information will enter.) 2. Start date, End date and notes are kept in a historical record. (Notes will be a separate table.) 3. Need to give them the ability to deactivate a person record. a. This would deactivate all records w…

Hi guys, perhaps a basic question, but unfortunatley I could not figure it out, and did not found anything searching the forum. I have two tables Route and Airpot: Route Airport ------------- ------------ Rte_From_ICAO Apt_ICAO Rte_To_ICAO Apt_Name Rte_Alt_ICAO etc.. .. When I create a Route in the Route table I want the name of the airport to be displayed (not saved in table) right beside the according ICAO entry. I managed doing this for the Rte_From_ICAO with a relation to Apt_ICAO. This works fine. But how to do this for To_ICAO and Alt_ICAO. Thanks a lot in advance Hans

I need to create an invoice On that invoice i need to be able to choose from a list of cutomers and then it will give me a choice of different contact for that customer with there information (phone, address....) Can someone draw me a relationship for that so that I can recreate it in filemaker. Thanks

Hi, i contact you as I have reopened a FileMaker db to follow electricity consumption. I have different prices acccording to hour of consumption. Then I created a DB i have 2 tables: - One table with 2 records corresponding to time slots when price is low and when it's not, with fields 'start time', 'end time' and price during this period, and price out of this range of time. - One table where I put all the consumption data with hours and consumption I linked the two tables with 2 links: one between begin time >= Hour one between end time <= Hour It does not seem to word, as when I try to calculate if the hour is within o…

Hey there, I'm still in the learning process (well, it will never end 😉). I created my first database (music database for sheet music and albums), with many layouts, and entered a lot of data (2500+). At the same time, I've been watching Richard's live training videos. My main problem was the data modeling part. So now that I know better, I have to clean the relationships and update my layouts, etc., IOW, apply what I've learned over the past months. Question: should I edit my 1st solution, duplicate/update base tables or TOs, update relationships, edit layouts / create new layouts? Or should I create a new solution and import the data from the old file? Th…

I have a FM solution for my school that operates smoothly on the income/student side, and now I'm trying to add functionality to the expenses and payments side. I have an expenses table that would allow me to list every purchase. I have a vendors table where I store entities such as instructors (independent contractors. i have no employees) and individuals I rent from. I have an accounts payable table where I store the things that I owe instructors or rent for (e.g. teaching hours, lodging, rent for separate months). This keeps track of how much I still owe. I can manually check off payments I've made so it stays up to date. But it would be b…

I made an application with four tables: Invoice, InvoiceLineItems,DailyLog, and Contacts. I would like the application to work as follows: On a daily basis, I enter info in DailyLog. That info would consist of the company name from Contacts, the date, a description, the hours. Other fields in this table are IDDailyLog auto serial number. Once a month, I would like to create an invoice for a given contact. I would like to have the company name, contact, address fill based on selection of company. Then I would like to pull in the information for that company for the month via a portal. I would like the portal to display the date, description, hours, rate, tot…

Hi, I am new to UUID in FileMaker. In the past, I used serial numbers as my primary key, but now I want to use UUID as my primary key. However, in the layout I want to create a display ID and retrieve other information because UUID is too long and unusable. How can I use UUID in the relationship, and use display id to retrieve the information from another table. Thanks. KC 1ToManyRelationship.fmp12

Hi all, We run a business that has been going for years and often deals with freelancers. I'd like to update our database so that we have separate layouts for contacts and for companies (currently with have multiple company entries, each for a different contact). The issue is that, for example, when a freelancer works for multiple companies, they end up with multiple contacts; OR when a supplier's rep changes job & works for another supplier, they again have multiple entries (if we delete the old contact we lose the contact info off the old orders). Also, just to keep things interesting, sometimes our suppliers can also be our customers. So, I'd like…

At the moment, I'm trying to tidy up some of the inventory databases that my company has spawned, and ideally have one solution covering everything. (At the moment data is spread over a few different, standalone files that have been made up ad-hoc for specific purpose). I've spent a bit of time working out what the users are going to need, and how we can re-use the existing data within a new structure. What's evolved is essentially a three-tier table structure, as follows: 1 - Products This table holds the products and packages that are offered to customers and invoiced for. The users are typically picking products (via a lookup portal) and adding them to…

I've been tasked with finding a way of tracking the number of customers that transact with our company every month. In our FM solution, we have a table of customers, which relates via an ID number to an order table (which in turn links to an Items table for the line items on each invoice) Some customers order multiple times each month, so I can't just use the number of orders for this metric. There is date information on each order and I need to figure out a way of relating that to customers successfully. Ultimately the output I'm looking for would be something like: Jan: 76 Feb: 89 Mar:66 And so on... I'm wondering if there's some…

Been awhile since I picked up FileMaker so I'm sure I'm missing some simple logic here but could use some help I would like to display a portal with grand-child records. I am using relationships to filter records. I am trying to show the status of who is out (unavailable) to train in one portal, and in another, who is active (available) and is an Attacker. The out table is working, but it's the active and attacker that is not. I've been walking through my tables/relationships to see where it is breaking.... This image shows that my first few filters are working. 99B65... is the foreign key that links a player's status to the training session. So you see Portal 5…

Hi, trying to see if this will work the way I need. Database 1, restricted to only to User 1 and User 2 has no access. Hosted on Filemaker server Database 2, User 2 allowed access and also hosted same Filemaker Server. But this database pulls data via relationships from Database 1. User 2 can open Database 2 ok, but it still asks them to authenticate to Database 1 as well? Anyway it can not need to do this and still have the relationships intact? Thanks

Good morning, I have a flat file (Excel) that has a weekStop Export Data 2023-01-01 to 01-07.csv of data with the following columns VehicleID, VehicleName, DriverID, DriverFirstName, DriverLastName, RouteID, RouteName, PatternID, PatternName, StopID, StopName, StopNumber, Timepoint, TripID, TripName, RunID, RunName, BlockID, Arrive, ArriveVariance, ScheduledArrive, Depart, DepartVariance, ScheduledDepart, Ons, Offs, ArrivalPassengers,VehicleCapacity, and DepartingPassengers. I created the following tables: Vehicle with VehicleID, VehicleName, VehcileCapacity Driver with DriverID, DriverFirstName, DriverLastName Route with PatternID, PatternNa…

Hi gang. I'm trying to get a portal working. This is a day trading journal. There will be multiple trades per day, so I'd like to summarize and show the total return (RR) for all trading days in a portal. I've been looking at this example from a previous post on the forum. As far as I can tell I've got all the pieces in place, but this portal is still simply showing all records in the related table. I've got a stripped down example file prepared, but I don't see how to attach it to my post as I see in others' posts. Here's the link. I imagine it's something simple, but I can't see it! Any help would be appreciated! Here's a screenshot from an Exce…

Hello all, I'm trying to wrap my head around something and would love some input. I'm converting a solution from spider web to anchor buoy. First, my previous setup involved a few tables where all the (base) TOs are connected to one another in somewhat of a mess and I have a number of layouts on those TOs. Part of this involves two tables - Addresses and Hydrants. Each address has hydrants related to it (between 2 and 5, but usually 3). These relationships will very rarely change (basically only when a hydrant is removed). Instead of a join table, I used a multi-value foreign key in addresses to relate to the hydrants. This is working perfectly both in the spide…

Hi All: So, I'm back in FileMaker 12 once again to make yet another database and I think I've gotten my wires crossed a bit. What I'd like to do is create a troubleshooting system as follows: First select a problem type (iPhone/iPad Issues) and hit next select a subtype (Accessibility). If the problem isn't listed in the first screen of radio buttons, show a "It's Something Else" radio button to show even more radio buttons with first level choices (if there truly is that many choices in the first-level problems table). loop through the first two until the most specific problem definition possible has been arrived at (Example: iPhone/iPad…

Say for example I had 100,000 employees who all make widgets every day. I have a record for each employee's productivity on each day. If Bob has worked 10,000 days, I have 10,000 records for Bob. Field A is the number of widgets and Field B is the date. I want to add a field to each record that calculates the earliest date where the employee made as many or more than the number of widgets he made on that day. If Bob made 200 widgets on 1/1/2020, and the first time time he ever made at least 200 widgets in a day was 5/5/2018, then I want a field that shows 5/5/2018 in his record for 1/1/2020. I know I can write a looping script that searches for Bob records with >=…

I have a hypothetical (but might become practical) question: I have a database of music tracks. Users can drag on tracks and they get stored on a cloud server (AWS S3) and FM stores a reference to that. FM can play the tracks (via javascript "wavesurfer") and see all kinds of data about the tracks. When users log into the solution they are recorded in a users table, which stores some of their preferences, as well as a list of tracks they've marked, so that can be restored when they next login. Now suppose I want to allow them to create multiple playlists. Normally I would create a playlist table and a join table with a list of playlist ID's and trackID's, and that would …

What is a simple way to test for this?

I have contact information available in several csv files. Each record represents a family of two with two first names and one last name plus a single address, and perhaps an e-mail address. I want to populate a relational database with this raw data, which will then be edited and missing information added. I’ve created two tables: FAMILIES and MEMBERS. One family can have many members. In FAMILIES I treat each record as a family with a single physical address. For convenience, I show both family MEMBERS in a portal in the FAMILY file. Available data from a “family” data file is imported into MEMBERS. Example of single import record: B…

I've searched diligently for a solution to this problem and was surprised there are not more solutions suggested. The best I've found is an .fp7 demo file for MagicKeys that I could not open; anyone have a later demo version of Magic Keys? Any other suggestions/methods will be appreciated!

t This might not be the best forum for this question; if not, feel free to move it to somewhere more appropriate. After running a find in one window, I want to display the found set in another window using a different layout. If I were willing to use the original window, this would be trivial: just switch to the other layout. One approach I've considered is to set up a self-join between two occurrences of the same underlying table (matching the table's key with itself) and use Go to Related Record (henceforth GTTR) to navigate from one layout to the other. The originating layout would display records from the first occurrent and the layout in the other wi…

I put this in Relationships which seems close to the right place, but I could be wrong. I am not sure how to describe this accurately, let alone search for a previous discussion . . . happy to be pointed to an existing thread. I have a three table instances, two tables involved. Table: EVENTS with fields: ID PrimaryParticipant_ID SecondaryParticipant_ID Points Table: PARTICIPANTS with Fields ID MostFrequent_SecondaryParticipant_ID This table has two instances: ParticipantsPrimary and ParticipantsSecondary …

Hi: More by accident than knowledge, I created an unmodifiable field, I inverted a lookup to populate a field, and forgot to delete the corresponding lookup on the other field . Functionally this "accident" is great for me, it prevents unintended changes, but I wonder if it may create other (not good) consequences. I made a simplified file which recreates the "accident", you will see that the fields on the panel cannot be modified (they can be deleted by clicking on the red button). Any comments are welcome, file is attached below. Non_Modifiable_invoice (1).fmp12

Hi there I'm a newbie but eager to learn!! Sorry if the question is absurd. 1. I create 2 new Tables (aka Base Tables) named Company and Contact. Both have a primary key. 2. In the relationship graph, I have the TOs and want to build a relationship between them (Anchor-Buoy). So I will need foreign keys in both. My question: Do I already create the foreign key fields in the Base Tables or only when building the relationship (TO)? Sorry again...😉

Hi Everyone, Bit of a head scratcher. I've got records (Table 2) in a portal on the page of Table 1) and that is sorted. The order of those records can be shown using a field Order_Number, set as a calculation of Get (RecordNumber). However it all starts to go wrong when I want to use Order_Numer in a Table 3. Order_Number always comes up as 1 in that Table 3 ... I'm presuming that for each record in the Table 3, it's in 1st place, as it's the only record that is being seen - so they all have the same first place, ie Get (RecordNumber) is always 1. I want to sort the Table 3 in the same way as the portal is (the sort in the portal is very complicated, and so i…

I've currently got this quarter working! This is a venue booking system. Three tables are: t_ROOMS t_EVENTS t_ARRANGEMENTS The idea is that you can have several events under the umbrella of one arrangement. (Think something like a conference that might have a Keynote speech in one space and several smaller meetings in other spaces. Or something that might go over days or weeks) Relationships are: t_EVENTS has a foreign key from t_ARRANGEMENTS::Arrangements_ID t_EVENTS has a foreign key from t_ROOMS::Room_ID I need to clash check against Date, Time, Room. I have it semi-working for Date only so far: I have…

I've been doing so much reading over the last week on value lists and relationships. I've tried so many variations that I thought should work, that I went back to a brand new database to check my sanity. My structure is three tables: Arrangements - Organisations - Contacts ' For clarity I don't have Contact Names or Organisations Names. I've got two Value Lists: Organisations (which is just an unfiltered list using Organisations::OrgID Contacts (which is from Contacts::ContactID. Including only relevant values starting from: Organisations) This works perfectly - I create a new record in Arrangement, choose from the OrgID Po…

I'll start by saying I think this should be simple but I've gone around in circles for a while and can't sort it out. I have a table of event information which includes addresses so I would like to be able to choose the locale (city, town or suburb) and populate the state and postcode from a separate postcodes table. However there are some duplicate town names and a variety of suburbs with the same postcode. When I create a value list of locales, only the first shows and hence only the looked up values for that record will be populated. I'm pretty sure a join table is what I need but I've tried to construct this a couple of times but can't get it. I'm convinced …

Hi there- new member here so I hope I am posting this in the correct place. I've been messing with two of my table relationships for a long time now and I can't seem to figure out if I've correctly related them. Some records are being displayed as desired in a particular portal, but not all records are. The portal does not have any filters on it so I'm expecting to see ALL related records. Here are my table relationships: Looking specifically at reports and tardiness & tardiness_reports tables, I'm wanting to see all of the tardiness records for a particular report date/time in the report layout. Here are my tardiness entries for March 31:…

I have a portal which includes related data from 4 fields and I would like to filter records so that only those which include certain values will show. I have created a global field each of the fields with a drop-down list of values from each of the for fields in the portal however by default they are blank. I can filter for fields which meet any of the drop down values however I can't find a way to stop records with empty values in any of those fields whose dropdown remains empty being included. Also the default appearance is filtered such that records with any of those fields empty are included - I want all records to show by default. The portal filter current…

I have a huge problem that I try to solve last 2 days. I just can't se any the solution. Maybe is there a trivial solution but I can't se it. I have 4 tables: Overlands Calculations Income Outcome Overlands is the main Table, and it can have multiple calculations, and each calculation can have multiple incomes and outcomes. The Calculation table has, for now, just 3 relevant fields. Cars, People, Earning. The Income and outcome tables have bit more relevant fields. 1) "Price with Tax" (is calculation of fields Price & Tax) 2) Grand Total price is a calculation from fields "Price wi…

Hi, first at all, I am a beginner. I am sure the problem is trivial, but after yesterday night, my brain is dead. I have just 2 "core" Tables, "People" and "Overlands". 1. occurrences of the People Table is "Drivers" 2. occurrences of the People Table is "Codrivers" I connect these 2 Tables called "Drivers" and "Codrivers" via join tables to a third "aggregate" Table called "Cars". The Cars table is connected to the "Overlands" Table. When I make a Portal in the "Overlands" Table that display the values from the "Cars" table, and put Fields from Drivers, Codrivers and Cars table, everything is OK, except that I can't show more than 1 Codriver in …

Good Morning All, I have a customer table and a quotes table. Each year 10-12 quotes are added in the quotes table for each customer and related with the field customer (which is a number field) In the quotes table, there is a company field and a premium field. I am trying to create an instance of the quotes table, that shows all 10-12 quotes on one row, across all customers. For example, if I go into an individual customer file, the portal will show all quotes for all customers. I created an instance of the quotes table, called quotes 2 and related it to the main customer table, however this is displaying each quotes record on every row in the table,…

I've mentioned this before. My background is in programming in a CAD/manufacturing environment. So, while I am familiar with coding and have been learning to write and develop Filemaker scripts for several years database setup and relationships are a weakness. That said, I am creating a system to submit lens orders for eyeglasses to a lab. The lens catalog is sent to each doctors office from the lab in xml format. Each catalog can have different content but the XML format is the same. I know how to write the xslt to import the catalog. What I'm struggling with is setting up the tables in a file. I created a file named after the vendor and in it I have created sev…

I am trying to create a library catalog for folders containing my personal and professional paper records. I have 8 filing cabinets containing 1-5 drawers for a total of 15 Locations. When my wife or my secretary need to file a new folder, They know where the new folder should be filed, i.e., which Location or cabinet-specific drawer, They have a sense of the category under which the folder should be filed, but cannot be expected to know the exact category name, and They have the authority to Name the folder based on their general understanding of my prefered naming conventions. I have been able to create a Value List, Locs, from a data…

Hello, I need help please understanding the relationship for making 3 levels multi-select picker from a list. Example: I have a list of 10 colors, if first table selects 7 colors, then the second table sees only 7 to pick from and if I pick 3 out of 7, then the 3rd table will see only 3 to pick from. I have the following tables 1) Context table (tbl_Context) = Anchor 2) Table with the list to pick from (tbl_List) 3) then I have 3 other tables table A, B, C my approach (Anchor-Buoy) so far as follows: -------------------------------------------------------------------------------- tbl_Context --> tbl_A tbl_Cont…

Hi all I can't work out what combination of relationship/portals/portal filters is best for performance. The first scenario is the customer record which has three portals that all ultimately point to the same Log table, the portals are mainly distinguished by the value of the Type field. They are all in their own tab panel so only one is displayed at a time. The portals also have a "free text" filter which refers to a global field, so users can search the portal. The portals display the following data: Recurring Services (type = 1 and isempty (invoice_idf)) - 100 records - sorted descending by Log_ID (number field) Jobs (type = 2) - 10,000 rec…

I have a field that is a calculated text field, made up from other fields in another table. The calculation looks like this: Pubs::Pub name & ", " & Pubs::Address & ", " & Pubs::Town & ", " & Pubs::County & ", " & Pubs::Country &" (" & Pubs::Operator & ")" When all of the referenced fields are populated it produces a nice tidy entry like this example: THREE GUINEAS, Station Hill, READING, Berkshire, England (FULLERS) However, often a field (usually but not exclusively Pubs::Address) is blank. This is correctly blank, but produces: THREE GUINEAS, , READING, Berkshire, England (FULLERS) which is ugly.…

Good morning. I have two tables joined by a field called Visit, which is simply a chronological number. For one entry in the first table, there can be many entries in the second table. I would like to renumber the Visit field in the first table. How can I force FM to automatically renumber the Visit field in the second table? Thanks

I've inherited a FM database, so I don't know all the ins and outs of how it was created. I *think* I have two tables that are not used anywhere, and that I can delete them, but (of course) I want to be sure. Per the Relationship graph, they don't have any relationship with any other tables. Is there an easy way to tell whether these tables and their data are entirely unconnected with the rest of the system, and are ok to delete?

Hello Forum. Seems like I'm getting back into FileMaker. But I'm pretty rusty and forgot many things. I have the following relationships (probably wrong for what I am trying to accomplish): The 'DataEntry' TO's are just for presentation/ U/I for iPhone, and the 'Gallons_VL' is used for a condtional value list. The 3 Main TO's are JOBS, TIME & GALLONS. A JOBS record can create 2 different types of child records-TIME or GALLONS, never both for 1 specific JOBS record. TIME and GALLONS Layouts are each based on JOBS with their own portals. The problem: Say I have 8 JOBS records, 5 with TIME child records and 3 with GALLONS child record. Re…

Hi all I'm implementing the ability to control cost pricing via separate records so there's a record history of changes. Currently the price table is related to the asset table and filtered via the relationship (Creation time stamp) to show the newest price for that asset and in turn being used in any transactions. It works just fine, however now I'm wanting to allow entering future pricing by adding a 'beginning' date field. For example: PRICING FOR ASSET X $450 (beginning) 10/01/2021 $400 (beginning) 01/01/2021 The price field for Asset X on the asset layout will display $400 until October 1 2021 rolls around despite there being a "newer" record wi…

Hi, I'm looking for a way to display and manage an m-to-n relationship in a portal. My goal is to have the tables Students, Courses and Attendance (the m-to-n table), like in the TO diagram. In a layout based on the Student table I'd like to display a portal with all courses and and a checkbox field to mark attendance, like in the screenshot. I have the feeling, there should be a pretty clean and easy solution to that, but I haven't found a good one yet. (Currently the portal displays the Attendance table, and to create records you have to select every course from a popup menu. That's not what I want in the interface because it's confusion for the user…

Hello, Beginner question: I am trying to understand all the methods for creating records in filemaker from simple to complex. Any good reference to read about the different methods including: When and how to use the best method for linked relations and not directly linked relations. Examples: 1) Direct creation (ticking create records) 2) Globals 3) Scripting 4) Portals 5) Creating a record through a join table etc Thanks

Hi all I'm trying to make a small application to track rental properties I have. Current issue is displaying the "active" tenant and lease terms. Tables: Property Lease Tenant Tenant is linked to Lease by a LeaseID (so multiple people can be on the same lease, if necessary). Lease is linked to Property by PropID. On my Property form I have a portal to display the current 'active' tenants(s) and the lease terms. 'Active' can be defined as their moveinDate and moveoutDate. If today's date falls within that date range they and the lease are considered "active" and must be shown in the portal. No active tenants? Portal is empty. Any suggest…

Hi. I am trying to create a simple single user DB to record purchasing and selling shares. I will have a table for companies, and one for holdings (open trades). So when I buy a number of shares from a company, it will be recorded the holdings table. When I sell those shares I want to link the sale record to the purchase record, and have the details recorded in another table called completed trades. The record in the holding table will disappear as well because they are no longer a current holding. My question is, does this require the 3 tables or am I on the wrong track??

Hi Everyone - I've been up for two days and am totally brain dead and I cannot for the life of me, remember how to take a list of results as seen here, and make them display the cumulative point totals for each person. Portals? Multiple Tables? I can't remember. Been too long and I'm getting too old - lol Thank you for your help!

Hi all I am further developing a solution I created a couple years ago that managed a project - in particular entertainment equipment rentals. Now I'm modifying the database to track multiple projects. In particular events that handle hundreds/thousands of products and assets that are rented from elsewhere and distributed by us. Essentially, my solution tracks who gets what on a project. All items are already barcoded by the original vendor and we use their Product and Asset codes for tracking and to aide in returning equipment to the owner(s) at the end of the job. Why keep records from finished jobs? Essentially old projects I need to keep records for should anythi…

I want to design a database to record the purchase and sale of shares including dividends. I can't even get past stage 1!!! In my mind I would have a company table, a holdings table, and a transactions table. But I cannot figure out how to wire this up?? I have attached my attempt. Can anyone please give me a kick start?? Thanks. Trades 210223 Copy.fmp12

It's been a while for me using FM, I've always sorted records by a related field and got records sorted without problem. Today this doesn't seem to work, execute 'Sort records' changes the initial sort order but they don't get properly sorted. This is happening in table view with records from two files hosted in FMS. Any idea of why this may be happening? Thank you in advance.

I find myself having to create global fields to store a constant to use in a relationship. It would be easier if I could use a constant in the relationship directly. Also, a global field can only be used as a 'left value' whereas a constant could be used on either side of a relationship. A constant would not need initialising each time the file was opened. This could be me not understanding something that could be done otherwise but would it be a suitable thing to suggest the platform developers?

Solved! Can't see how to delete this post so ... I had thought that "Go To Related Record From" mean the records that you were going From ... turns out it's the records that you're going To! 😳 ✌️ 🥳 Hi Everyone, Hope you can help as I've been banging my head against this one for over an hour, and I just can't figure it out! (I'm on FMP 16) I have two tables: People & Pictures They're related by a relationship called People_Pics_Relationship, and in that People::ID_Person = Pictures::ID_Person I have a layout called "Pics" based on this relationship People_Pics_Relationship, filled with fields the Pictures Table…

Hi, I have attached a mock-up database and schematic to explain this. Basically, I have a list of animals with lots of vet history. One aspect we want to track is the breeding history for each animal. Each female can be a Sam (mother), each male a Sire (father) and all animals could be Offspring. I would like to look at the record for each Dam and Sire and see their offspring, as well as looking at each Offspring and seeing their parents. This could, of course, end up multi-generational where an individual animal is both a parent (Dam or Sire) and an Offspring. Each animal has a unique ID. I have tried creating a Dam ID for each Dam, a Sire ID for each Sire and an O…

Hi all, so I have 2 tables CUSTOMER and CONTACT, in the contact one of them is listed as "billing" contact (it's a simple yes or no field ). I have a form that prints showing customer information (Name, address, phone # ...) and I would like to have the contact that is listed as "BILLING" contact to appear on the form .... How would I go about this one ...

Hi, having a few issues creating another instnace of a table so I can have a another relationship But it seems to be having issues pulling data from the related record Current relationships- Company ---- Contacts Company ----- Certifications I have created an additional relationship Contacts ---- Certifications 2 based on the contacts name. This works and a portal in the Contact record shows the correct certifications But I have created a new layout using the Certifications 2 table, and I want to display a field from Contacts, but it just won't show and data, like the relationship isn't working. Any ideas?

Okay, here's my scoop. I'm importing updates from a text file into an existing database. The text file has work assignments for a given period (say month). I have a text parsing script that breaks down the critical info into separate fields (jobname, jobstart, joblocation, etc). So far, so good. I have a related table with worker names. The goal is to be able to both assign jobs to people and to summarize the jobs already assigned to people. My problem is that the text file isn't always consistent. Sometimes Smith, Joe. Sometimes Smith, Joseph. Even Smith, Joseph P - you get it. I have a custom function that pulls out First, Last, Middle, etc. …

How can this dropdown only be values of "subdivisions" that are located within the designated "county" designated above? Also would like to be able to add new records here if they don't already exist. Secondarily and not as important.. I would love to have a type-ahead feature for this dropdown because I have over 1500 “subdivision” records. TEST-SubsfromCounty.fmp12

My problem is this: I have a range of related tables: Species related to Permits Species related to Ethics Applications Species related to Research Projects Projects (and Permits and Ethics Applications) are all related to other tables (eg people) Setting up the many-to-many relationships for these, FM creates new table occurrences for Projects (Projects 2) to relate to Species and for Permits (Permits 2) to relate to Species. On the Species layout with portals for each of these all works. Each portal shows the records to Species from Projects (2), Permits (2) and Ethics Applications (master occurrence). However, when looking at …

I have tried every imaginable combination and am still coming up short. I need the "addressArea" to auto populate based on the given "county" and "township". The attached PDF shows my problem. I am also attaching the FM file incase that helps. Any help would be appreciated. help2.pdf gprtrack004-help2.fmp12

Hi, I tried to find my answer in previous posts... but I am not familiar enough with programming so I could not understand... What I want to do. I have table1 with 2 fields. A number and a name. I have many records, each with their name associated to a number. In table2 I have a more complexe table with several fields. Two of them are that number and name. In that data base, those number/name can come back many times. I would like to be able to just enter the number and have the name auto-fill automatically. I did create a relationship between those fields across the tables. Then on table2, I told the Name Field to use lookup and copy value f…

Help! My brain is stuck. Here's the situation. I'm using filemaker to track/schedule/budget classes for a college department. I've got tables for faculty, classes, class sections, etc. So far, so good. Once the schedule is set, say for the spring semester, I'm getting daily updates on enrollments - sometimes changing room assignments or faculty, etc. So for the spring semester I'll have multiple updates. Most of the info is identical update to update (i.e. class number doesn't change, enrollment cap, blah, blah) but other info is changing. Here's where I'm stuck. Ideally, I'd like to track those changes update to update and either highlig…

HI, I am starting the process of building a database to export one CSV file from various different suppliers. My suppliers are all providing their data in different ways and with different fields. Some will have bullet points and description, some will have tags, others not, etc. During the course of a day, I will import data from up to 10 different suppliers, sometimes up to 3 times per day. I need to treat each supplier's data differently to get the same unified output CSV file from each supplier, in fact ideally 1 large combined, unified output CSV file for all suppliers. What is the best way to structure such a database? Should I have a table for …

Hello all, I am working on an archaeological database for my PhD, and would like some advice on the following issue: My database is for objects, collecting information on chronology, provenance, material, dimensions, etc. I have a unique object id # field, which is auto-generated. However, I realize that for my PhD, it doesn't actually make sense for me to have the objects be allocated random numbers. I want to be able to present my data with a numbering system based on material (ivory, bone, terra-cotta, etc). So instead of having object 438 or object 6475, which is basically meaningless, I would have M1, M2, M3 ... for metal objects, S1, S2, S3 for stone, etc.…

I have wrestled with this, but am failing. I have Students and Supervisors. Supervisors can have more than 1 student, and students will definitely have more than one supervisor. I would like to look at a student record and see their supervisors, and look at a Supervisors record to see all their students. I am assuming this should be in a portal, but I am having trouble with the self joining aspect. I want to keep them in the one contacts list, as there are other tables (eg Events) that both students and supervisors and other contacts will relate to. That stuff all works well. I think I need a many to many self-join, but I seem to be stuffing it up. I have c…

Good morning - so this is something I have struggled with for some time and it seems easy enough but I can't seem to figure it out. For every simple relationship in my database, I can very easily show related records. It's literally one direct related record away and can be processed easily in formulas, lists, scripted - it just works. However, I struggle when more than one relationship is involved. As an example, assume I want my solution to manage a team of physicians in different insurance networks. So my basic tables are: PHYSICIANS INSURANCE then I have other tables, including: SPECIALTY (of the physician) CRE…

Hi, I have Client Table and Certifications table, I have already a relationship between the 2 tables based based on Company name. Some Companies have another name, so i'd to make a relationship also if Company name on the Certifications table also matches Company name2 of the Client table. Is this possible? Thanks

How to use "allow creation of records"...and "delete related records in this table"...in the edit relationship. I have attached a file, and I know how to use the "allow creation" and "Delete related records" on the right side(Invoicelineitem). When I delete the records, I know it would delete the line items when I check "delete related records" in the invoice lineline items. I don't know how to use the allow creation of records and delete related records on the left side(Invoice) in the edit relationship. Thanks for your help Regards, KC TestRelationship.fmp12

Hi everyone. I am new to this forum, so I hope I am posting in the right spot. I created a database for my company at work. On multiple screens I have a patient lookup. The relationship is patient table: last name = insurance table: last name lookup. The lookup is a drop down that shows patient last name and first name and then pulls other corresponding information when selected. The lookup works, however it only pulls one record. Example. If I have multiple patients with the same last name like smith only one smith shows up in the box. I need all the smiths to show up to pick the right patient. It seems like it should be such a simple thing but it isn't pulling …

I'm working in FM11 and trying to figure out a conditional value list issue. Let me setup of what I have and what i want to do: Table#1 - called "Main" 1) In this table, I have a text field called "Product Code" which is also a drop down list consists of "GC" & "VSC". 2) In this table, I have another text field called "Material List". Table#2 - called "Material Archive" 1) In this table, I have a text field called "Materials Available" which has 20 records created. 2) In this table, I have another number field called "Serial1" where 11 of the 20 material records are earmarked with a "1" in the Serial1 field. 3) In this table, …

+----- Code.fmp12e ------+ tables ... Users Doctors Lens Info Drugs Diagnosis Codes ... Equipment *** (Manufacturer, Model Number, Com Port, Speed, Bytes, Bits, ... Installed (yes/no). 1 record for each piece of equipment we support. ) ... +----------------------+ +----Exam.fmp12 --------+ tables ... Exam *** (Eye exam data for a patient. Multiple exams per patient) ... +----------------------+ I am working with an Medical Records / Office Management system for eye doctors, optometrists. We provide our solution to multiple customers across the country. O…

Hi, I have a database with a few relationships setup, and is pretty much working as expected. Pic attached Assessors (contacts) can be added from the Company (Member) record via a portal and all links nicely Certification records link to the Company, and show all relevant certifications done by Applicants and assessed that Company via a portal. The Certification records are bulk imported from another system and match by the company name In the Assessor (contacts) record, a portal shows all their training courses they have done / doing via a portal to the Assessor Training table and you can add a record here And in the company (member) record I ca…

Hello, If I may pick your brains regarding this relationship dilemma I'm struggling with... (sample file attached) I have a file that will narrow down options to choose from in a popup menu (global_presetsList) based on which account is logged in. That part is functioning correctly. (scripts to switch users — u1, u2 — available in Scripts menu; accounts have no passwords) What I need for the bottom portal (result for specific preset) to show is values belonging to that specific option (presets in my file). As such: current account > leads to presets > which leads to end values (parts). What it is currently doing is showing options based on accoun…

Hello, I'm trying to look at the best way to set up 2 different projects that have the same question. Both of these could be solved with a 1 to Many relationship with a details table, but I'm wondering if a single field in the record that I could append and delete from would be more helpful (and less records) Note: I only have 4 users and do not need this to scale. 1. I'd like to add keywords to a database containing photos PhotoID Container (reference) Keywords 2. I'd like to list the users that have viewed a record at least once. - I am not interested in how many times or when they visited. I just want to know who has seen…

I have an experimental file I am looking at. It has a company table, a contacts table, and a join table. One company can have may contacts, and one contact can have many companies. I have inserted 2 portals into the contacts table. One based on the join table, and one based on the company table. The portal based on join works fine. The one based on the company table only shows the one record repeating (Record 2 in this case, see below). As I say this is just experimenting. My question is this. Is it only showing record 2 in the portal based on 'Company' because going through the join TOC to the company TOC, you will only ever see the first record?? Thank…

Dear all I am working on a photo catalogue. Some images need to be cross referred eg all taken at same event So I have a join table that connects Id numbers for different images Two different problems arise 1) Permutation if I have say 15 images linked to a starting image how can I automatically generate links between the others eg If image1 connects to Image2 … image15 I need to have separate connections between image2 and image15 etc 2) commutation If ImageA connects to imageB and ImageB connects to imageC ImageA should also connect to imageC - but there's a clear danger of circularity/looping Any suggestions…

I noticed that a central table in my database, which used to have its own custom order, is now ordered alphabetically. I haven't used my database in some time, so it's possible I decided to do this a while ago (I deliberately put some tables in alphabetical order). But I do not think I would have had any good reason to. The same thing—loss of custom order—appears to have happened to me once before, several years ago. Is it possible inadvertently to impose an alphabetical sort on a previously non-alphabetical order? Knowing how the custom order might have been lost could help help me to avoid future occurrences. And is there a way to recover the old order? I imag…

A newbie here. I just wasted a couple of FM Starting Point files after days of work so I'm here. My scheme is simple: I'm creating a solution for a press house and I'm adapting the FM Starting Point to a degree for job tracking and inventory. My basic entities are the following: Authors for each book or books. (I created a new table for Authors duplicating the Contacts one) Books in editorial process assigned to one or several authors, to editorial staff (editors, designers) and to external suppliers. I'm using Projects for these books in progress. But then I have the already completed and printed book with the same title and I u…

I'm trying to figure out the best way to structure a database to hold a list of Keystones (a six sided object made up for 4 sides that are squares, and 2 sides that are trapezoids). I need an object Polygon representing each side of the Keystone (because I will render these Keystones in 3-space and won't always render all Polygons depending on the view). Each Polygon shares its 4 corners with neighboring Polygons. And each of the 8 (x,y,z) Points defining the Keystone map to 3 different Polygons (every corner of the Keystone is the meeting point of three Polygon sides). There is a 1:many relationship with Keystones:Polygons. No problem. One table of Polygons …

I think I'm in over my head. I drew this out on paper, but I can't get this to work. I'm trying to emulate the behavior below. The column on the right, "Genre, Gameplay, Prospective" .. to where I pick the item by name "Action, Arcade, Chess" etc in the first column of the portal, and it shows the associated sub category next to it. The choice would be entered by either by Genre_ID or Genre_Name and then the genre_category_id & genre_category from the same record would show with it. The unique value being 'genre_ID', and it's related to 'Game_ID' on the main table. Game_ID is a unique value, but it's not -the- unique identifier for the re…

The tables. Items::ID Items::Item Items::Publisher Items::Date The Genre data is already arranged like this: Genre::ID (Unique Key) Genre::Name Genre::Category_ID Genre::Category The Platform data is 'simple'. ID and Name. But each item could be multiple platforms. (Example #1 vs. Example #2) Platforms::ID Platforms::Name An item can have multiple Genre Categories related to it, and those usually have a single choice from within their Category, but might have multiples. It's supporting the possible multiples that I'm trying to work out. At this point there's 15 possible Genre Categories that each Item could have a selecti…