Relationships

Hi, I am trying to get the cost from a matrix table. My question is that is it ok to use combine ID using other table ID and use it as a link to connect to other table and get the cost from the matrix, instead of using primary key? Like using a combination of foreign id key to make one unique key to connect to other table with the same key to get the data. Is that ok? If it is not conventional, what is a better proposal? Many Thanks. Text Matrix.fmp12

Hi Everybody, First time, long time. I inherited a FM solution that I'm trying to pick apart to understand. One aspect is that table occurrences were named with default names (table name 1, table name 2, etc). Another aspect is that some tables are related to multiple occurrences of the same table (table 1 has two relationships based on the same key to table 2, table 2's occurrences have default names, i.e. table 2_1, and table 2_2). So here I come, trying to figure out what is going on. My question is, how can I determine if it is safe to delete a table occurrence (such as table 2_1 in the above example)? I seem to get the same error when I go to delete t…

I've been a flat file creator forever, as the best part of the Relational database, to me was 'voodoo that I'll figure out later'. Now that some more bits of using related fields makes sense to me, what is the proper way to do it? I want to 'clean up' now before my file grows more. Tables: Records Vendors Publishers In a database for collecting items, 'Records' is the main table. Items come from vendors, or publishers, or .. (whatever) I am also keeping a table of vendors, publishers and their data. But in the Records table I only want to display the vendor name. Vendors and Publishers are entered on the Records layout. The table…

Hi, I am new to filemaker. I have created a crosstab table and I like to retrieve data from it in another table. For example, in the layout, "RetrieveData", I like to retrieve the activity name "Apple" in March, I like to get the value "24". How I should approach it in the relationship. Many Thanks. Kent CrossTabInputExample.fmp12

At the beginning I had a "customer file" database made up of : - 1 "CUSTOMER" table (= companies...) - 1 "CONTACT" table (=people who work there). - 1 join table " customer contacts " (allowing a relation of several to several between the 2) - 1 CUSTOMER model with a "contacts" portal (which lists the contacts) - 1 CONTACT model with an "employers" portal (list of clients to which the contact is linked) so far nothing very complicated In the real life of a VSE, 1 contact often comes down to a name + 1 mail + 1 such which "turns" sometimes into 1 professional or private customer but he can also place an order for 1 third party (an association or his wife h…

I’m using some of the current downtime to tidy up some of the murkier corners of my Filemaker solution. One of the problems in my crosshairs is a badly-chosen match field that I want to replace. The relationship in question is between a catalogue table (a list of all the stock items that we have available) and an Order_items table, which is the line items on customer invoices. Foolishly, I chose to use a website URL as the match field for the relationship between the two. At the time, all of the items in the catalogue had a matching page on a website, and it was by definition unique. Seemed like a good shortcut at the time, but of course we’ve since …

I'm trying to figure out the best way to structure my data, so that I could "merge" duplicate parent records and combine all of the child records. For example: "ABC Enterprises" has a number of child records and linked records (contact names, work orders, asset records, etc.) - meanwhile there are second and third records "ABC Enterprises Ltd." and "ABC Enterprise" that has similar child and linked records. I've attached a file with some sample records. This example is overly simple to illustrate what I'm trying to do. In real-world, each company would have many other pieces of information (address & contact info, warranty documents, etc.) acros…

I have a database setup wherein I take post-production notes on an animated series. I have created a relationship where a table occurrence of my main table (retakes) named (retakes_listfind) is present. This is a relationship where a text field named "listfind" in retakes_listfind is set to equal a text field named "scenenumber" in retakes. I can paste a list of incoming scenes (1, 2, 3, 4, 5, etc.) in a retakes_listfind layout with a portal to retakes. Here is the issue. It works fine, but only if the numbers match. Due to an accounting requirement we need to enter all scene numbers with a leading zero, and it isn't a pure number because often scenes have text addit…

Hello, Here is my scenario. I have two tables. One with dog houses, each record has information about the doghouse, etc. Second I have a table with each individual dog, each dog is linked to a doghouse by a housing ID. Each dog also has a status of "Active" or "Otherwise." What I want is each doghouse to have a count of how many "Active" dogs are linked to it. I can then do searches and calculations based on that. Is there a way to have a doghouse record value that is a calculation, or will I need to write up a script that is run every time I want a report. As a side note I have some 6,000 doghouses and close to 60,000 dogs registered. The faster it works …

Hello FM geniuses, I've been trying to work out the solution to this issue for a few months now, and am losing my mind! Essentially, in Table A I have a summary field of the total sum of commissions (all records). I want to show that data in a sub-summary part in Table B (sorted by Month). It seemed that using a Looked-Up Value would provide the solution I need, but alas, it doesn't even display the data. Apologies if my explanation is lacking important details. Please let me know and I'll do my best to elaborate further. For the record, I'm using FileMaker Pro 18 Advanced on Mac. This database is used on a desktop, and I'm the only one using…

Hi, I'm having trouble getting a relationship up and running and am hoping there's some little detail I am missing that someone more experienced will be able to help me with. I have a table called Availability with a calculation field called CalcAvailability that uses a Case function to evaluation several different conditions and display the words "Available" or "Blocked" This table also has a foreign key field for ClassPeriodID. The table I want it to relate to is called Class Periods, which of course has the Primary Key Class Period ID, and a field called Available that just has the word "Available" in it. I have configured this "Available" field in diff…

I'm getting the ol' "There cannot be more than one relational path between any two tables in one graph." alert. The main set-up of my database is to track Prints and Image. Images are the parent record and they can have multiple Print children. I have also created a table of Groupings, where I can group Prints and Images together, for an exhibition idea--so both Prints and Images can be grouped. Since Prints and Images can belong to multiple Groupings, and Groupings can have multiple Images and Prints, I have set-up a join table. The problem is, from the main Images layout, I want to be able to attach that Image to a Grouping, but in order to do…

I am trying to establish what should be a simple relationship between two tables. I am a physician, and this DB will hopefully make documenting rounds easier. I believe that I’ve done this sort of relationship before, but it is not working here. DB #1 Admissions: has information such as the patient’s ID#, the hospital name, hospital number, date of admission etc. There is a calculated field yielding text “true” or “false” if the patient is still in the hospital. DB #2 Rounds notes. Fields for vitals, objective finding, subjective, etc. What I would like to do is have in DB2 a drop down menu for patients still in the hospital. I have established a variable i…

I have a gardening database, with 12 Tables. I use it to keep track of my plants, their purchase, their bloom history, their planting locations, etc. I have the following as part of my relationships: Plant has a one-to-many with Plantings - Primary Key in Plants, Foreign Key in Plantings Plantings has a one-to-many with Locations - Primary Key in Locations, Foreign Key in Plantings Plant has a one-to-many with Species - Primary Key in Species, Foreign Key in Plants Species has a one-to-many with Genus - Primary Key in Genus, Foreign Key in Species I want to be able to display, for example, a form with my Asters, showin…

I am developing a database for my scout headquarters and it has the following structure that is shown in the attached image. But we are facing a problem because we would like the ID of both the scout and the team to be unique and sequential. Will it be possible? I appreciate your help;)

Greets, all: I'm stuck trying to figure out how to best accomplish the following; I figure it's a relationship question so I hope I posted this puzzler in the correct forum; if not, please move it to the right one. I have a Student (parent) table which pushes comments into a Comment (child) table, so it's a one-to-many relationship because each student can have many comments. I put a checkbox'd value list in each portal row so that users can tick which recipients should be sent the comment; they'd then click on the SEND button to the right of the list to send that (child table's) comment to every ticked recipient. Where I'm stuck is how to gather all the ti…

Hello - I'm not sure if this is the right place to put this, but let's have a go at it. I have a "PROPERTY" table that shows stats of homes for my users. In this table there's fields from a related table, "PHOTOS" that displays Default photo(s) for that property in 6 containers. When we add photos, new records are created in the PHOTOS table with our user ID and property ID. There's always at least ONE default photo in the first container window What I'm trying to figure out is this: When a basic user (not us - administrators) visits the PROPERTY table: 1) What's the best way to display the default photos if the user doesn't have any added, and …

Hello to all I have a come up to a brick wall I'm nor sure if this can be done but I will try to explain in detail. I am building a project database and ever project as a main ID I have set the ID's as the following: 2020.500 | 2020.501 | 2020.502 ect it increases by 1 the year will change every year on the 1st January to 2021 ect. Each project number will have samples attached to it but what I am trying to do is say the project ID is 2020.503 can I add a sample with the sample ID as 2020-503.01 2020-503.02 2020-503.04 and so on The number in red is the main project ID and the number in green will be each sample added to the projec…

I wish to include on a form view layout a portal showing sub-summary totals of related records from a child table that is a few relations away. I have found solutions in this forum and elsewhere for similar setups but they have yet to generate expected results. Relationships: System –< Assembly –< AssemblyItem >— Part >— Material A System has multiple Assemblies and each Assembly has multiple AssemblyItems. Each AssemblyItem consists of a single Part made from one of several Materials. Objective: Display a portal on System-based layout that shows total number of Parts in the System by Material type. Example: As a sim…

I have an auction calendar (layout - "Calendar") with properties that display address, date, time and location data on it from the PROPERTY table. When users log in and enter the layout, they see the calendar for the current day. These properties are common for everyone, and have a unique "PropertyID". I have two additional tables with key ID fields - the first is a USERS table with basic info about the user including their "UserID". The second is the USERPROPDATA table with a "UserID" field and "PropertyID" field. What I'm trying to accomplish is to allow the USERS to add their own pricing data (that only they can see) into a couple of fields from the USERPR…

Hi, I have 2 Drop down lists on my Layout which are populated from another table. I have created 2 separate value lists for both the drop downs, but they still change together. As shown in the image below. When i choose a member from the Member name drop down the name also automatically changes in the related to Drop Down List and vice versa. How should i stop that.

I have 2 table: Test7 and Test8 where related with ItemNumber. Test7 fields: ItemNumber,Part,Qty,Minutes,SumMinutes, SumQty,,Total7 Total calculation: GetSummary ( SumMinute;ItemNumber ) * GetSummary ( SumQty;ItemNumber ) Also sortsummary by ItemNumber. Test8 fields: ItemNumber, Total8, Used, TotalUsed (is calculation =Total8*Used) My question is, how Test8:Total8 lookup value from Test7:Total7 Thank you note: how to upload attchment from my PC to this case.

Hello. I've worked my way through a fair chunk of the Missing Manual for FM14 and am trying to create what is a pretty simple solution. I recognize that I could go a long way up the wrong tree if my my overall schema is poor so I'd love some guidance that I expect will be intuitive to those of you with more experience. As a "customer", I am essentially trying to track and compare the items within a rental order through its steps (which I think of as versions): Items ordered by us (qty and generic type, ie, "35 widgets, 10 gizmos") Paperwork confirmation of the order from the rental house (qty of some generic items ("35 widgets") but also unique SKU's o…

It seems that WebDirect can't access a field under another field. This will be a problem for several solutions that I've set up over the years and I wonder if there is a better method for the following: For example, in an Orders table and layout, there is a field Requestor ID that contains a foreign key that would match the Primary key in the Personnel Table. For each Order record, there is only one Requestor. The Personnel table of course contains name, contact info, etc. for potential requestors. In the orders layout, I have an underlying field (Orders::Requestor ID), that is set for browse but not find, and set to a dropdown value list of the Personnel Tab…

Interesting problem that I haven't run into in a while, and I can't remember how to do it. I have a reference table that contains the fields weight and zone. For each weight there is a different cost for the corresponding zone I have a table that list the zone and weight of multiple items. I need to lookup the package cost for the items based on the weight and zone. A little help?

Hi, I may be in the wrong subforum for this ... but it's related to relationships, so we'll give it a shot. If I have a list of people, and each of those people has one or more related records with a date field , any thoughts on the best way to find the next lowest date field compared to a given date. Example, Bob has related records with the dates Jan 1, 2017, July 15, 2017, and Jan 31, 2018 -- I want to find the date that is the date thats' closest to, but before, Dec 1, 2017. And, if Bob had not had any dates before Dec 1, 2017, I get effectively a null. Thoughts on best way to do this? Happy for it to be some sort of relationship, calculation, or…

Hi I am very new to file maker and I need help. I have to tables I am trying to connect. One table has employee information including employee number, name, etc. The other is a table where two employees make up a team or "unit". On the Unit table I have employee #1 and #2. I am trying to figure out how to be able to relate each employee to the Personnel table. I have a feeling this is a very basic question. Any help is greatly appreciate! Here is a screen shot of the tables I have set up now. Only employee #1 can see info on the personnel table.

I have a table DRUG.ADMINISTRATION with 2 table occurrences relating to the tables EXAMS, and ANAESTHESIA, both of which I'm considering as parents as DRUG.ADMINISTRATION records need to be created from each of these tables via a portal on their layouts. Within DRUG.ADMINISTRATION there is a field DATE which should capture the DATE of the parent. Currently, I have two table occurrences of DRUG.ADMINISTRATION; one setup with a relationship to EXAMS via DATE and EXAM.UUID and one setup with a relationship to ANAESTHESIA via DATE and ANAESTHESIA.UUID. This works fine when creating records, but if the DATE field is modified in the parent table DATE is not updated in the …

I have a simple database with two tables; Owners and Pets. One owner can have many pets. The pets table also has the field for type, such as dog or cat. I am having problems creating a portal to display just the dog owners. Have tried various things including a field with the word "Dog" hard wired into it with an = to type on the other side of the relationship. Any help would be appreciated please. I have attached my database. Pets 190802.fmp12

I am in the process of creating an app that will be a support for assistants to severely ill patients. The handling of medication, food, etc. One function should be the opportunity to track how the user is feeling and different health indicators. The idea is that one or more times per day you will enter how the person is feeling on a scale of 1-5 or 1-10. What one chooses to track should be able to vary from user to user. It can be things like sleep, stomach functions, epileptic seizures, etc. The idea is also that one should be able to follow the development of these indicators in some form of dashboard. How would you structure a function like this? I have come a bit on …

Hi, I have two related tables in my file, I want the portal to display related records wend "g_Color" or "g_Type" fields are empty. I know the relationship parameters in g_Color and g_Type are wrong for my purpose, but I don't know how they should be. Home:ID_Key = Cars:ID_Key Home:g_Type = Cars:Type Home:g_Color = Cars:Color Thanks in advance... Portal_filter.fmp12

I am a new user to Filemaker. played around with it years ago and basically understand it can be a much more powerful version of excel. I basically want to create a simple compare and contrast list for products for our workplace. Each Product / Record --- lets just say they are Hammers.... would have individual fields for their brand name, model number, material, height, weight, etc..... Many websites have features that when you are browsing their various products you can click a box that says add to list. Then after that, you can click a button that usually says "compare selected" that will show 2-4 or more models in a side by side vertical table so you ca…

Hi everyone. No doubt this probably has a simple fix but I just can't work it out. I have 2 tables; CARDS and TYPE. The TYPE table simply has a PK and a 'type' field. The CARDS table has several fields and is the child to the type table (one TYPE can have many CARDS). My data entry is on the CARDS table which has a dropdown TYPE field related to the TYPE table. Problem is when I enter a new card and use one of the dropdown type options such as LYNDA, the LYNDA entry duplicates in the TYPE table. I end up with numerous LYNDA's and other entries. Any advice is appreciated. Thanks

All files/tables in this discussion are fmp12 using FM 17 and both files are opened when initiating the app. "logging" is opened as the primary file and "claims" is opened as an external data source. I have a table called "newclaim" in "logging" which contains mailing address information including a USPS zip code. I have a table called "lu_ZipCode" in "claims". There is a relationship set between "newclaim" and "zipcode" on a field called "COID" (company ID). This field is automatically populated in both tables with "DCSI". This is an old method (used in Fm6) of establishing a simple relationship so that global fields can be used to pass parameters into scripts for z…

Hello, I am working on a financial system and I want help or a working example of an Account A to Account B transfer method in Filemaker. Account A and Account B are both in the same table ACCOUNTS and the transaction will take place in the LEDGER table. Attached an image as an example to understand my query.

I am trying to create a value list that can be used in a dropdown menu on a calendar layout. I have a Coaches table. Each recored contains the name and info of a coach. In the record there are two fields for dates titled First Date In and Last Date In. These dates indicate when the coach is booked to be in the school next. The calendar layout is a weekly calendar showing 7 days at a time. You can scroll thought the weeks with two arrow buttons (left and right). There are calculation fields (date1, date2, date3...date7) that calculate the dates for each day of that week based on the week of the year. As you click through the weeks I want to be able to see which coa…

Hi everybody, I recently amended a db from an old Lynda course, controlling shore excursions for river cruise ships. For my limited expertise (I am still a rookie at the very best) the construction is somewhat complex as there is many vessels, different routes, different charterers and consequently different shore excursion programs and prices. In some cases the shore excursion program of different vessels and operators is similar but not the same and so on. I have 7 different tables partly related (pls see graph attached): For every journey ::customers and ::trip are assigned a tour code and date (“tour code” is not unique as journeys repeat …

I want to be able to delete a record from Table A, but also have all of it's related records from Tables B and C deleted as well. Is there an easy way to do this, or do I need a delete button or menu option to go to a layout of each table, find the records based on the ID from Table A, and delete them? Thanks for the help!

Beginner (We all started somewhere, right?) Let's say I have two simple tables: • Clients (Client ID, Client Name) • Invoice (Client ID (related from Client), Client Name, product name, quantity, price, subtotal(=quantity*price) and Client Total (Summary = Total of subtotal) On the invoice, I am getting the totals of ALL of the invoices from every client.:( I want to show the total of all of a single client's invoices - I would like to show this not only on the client details page, but maybe on the invoice itself. So, the invoice would have a total of the current invoice, and a "grand total" of all of the other invoices for that same client

Hi guys! I'm about to embark on a project where I'm solely responsible for tracking a lot of different items distributed amongst 20 people. Barcodes were my solution and and the app I've put together works a treat. However, I'm trying to use checkboxes to filter out related records in a portal. When I try to implement this the portal goes blank, which isn't the result I expected. The two tables I'm having an issue with are "Projects" and "Assignments". The "Assignments" portal is on the "Projects" layout. The relationship is: Assignments::ProjectID = Projects::ProjectsID This accurately displays all the related records in the portal. However, when I ad…

Hello, In my fire department I have a table PERSONNEL RECORDS, I put a field on there LIST SELECT TO SMS. In that field I have a check box value list assigned like "Co. 1, Co. 2, All FD Officers, etc" each record in the personnel record table will get a variety of these check boxes checked of to match which group they are currently in. I have a separate layout and table (MESSAGESSMS) that a chief officer will go to and write a message to be sent out using the AWS SMS service, which I have working. In the personnel record table I have the members mobile phone numbers filtered and a separate field (ListOfMoblePhones) that lists all the numbers in the found se…

I'm modifying an existing database. The records I'm working with are estimates. Each estimate has a estimate number in this format: 16-123. The client sometimes makes duplicate versions of existing estimates. They then have the format 16-123-2, 16-123-3, etc. There is a field (Version) that indicates what version you're looking at. If there are 5 versions of an estimate, the version field will display the number 1-5 as you flip through the records. The client wants the Version field to be a drop down that displays the number of versions the estimate has. They want to select a version from the drop down and have that record load. For example, if there are 5 versi…

Hi everyone, I have two separate databases connected by a relationship. In Database A, I input items and their costs – sometimes it’s a reimbursement and these costs are minus numbers. I then have an almost identical layout in Database B where I use lookup fields to pull the relevant costs. This works great with positive numbers but when they are negative, the lookup fields pulls the absolute value i.e the correct number but in a positive. Does anyone know why this might be happening and how I can fix it? Hope that made sense and huge thank you in advance. Tearing my hair out a bit with this one!

So i have an inventory database and a customer wants table where you can specify what they are looking for. There is a list of 4 criteria (item, diameter, manufacturer and model) where the item always has a value while the other fields may be left blank. On the customer wants side, i have the portal filtering to match the criteria chosen and a refresh portal script trigger on the 4 fields. It seems to work fine. Here it is for reference: Case ( Customer Wants::Diameter = "" and Customer Wants::Manufacturer = "" and Customer Wants::Model = "" and Customer Wants::SC ≠ ""; Customer Wants::SC = Inventory::SC; Customer Wants::Diameter = "" and Customer Wants::Manufact…

I have a database that has the tables customers, estimates and inspections. (there are other tables as well) Anyway i currently have a layout per customer which has two portals, one that displays the estimates per customer, one that displays the inspections.When you click on one it takes you to the details of that "form". It works nicely but now they want to see one portal with the estimates & inspections listed in one portal. I thought what I would need is a join table but somehow I have become baffled. All three tables have the customer id. Inspections & Estimates have appliance ids as well. The end result is to view by customer all of the estimates and inspecti…

Here is my situation. I have a 25x25 grid - let's call it a map. Think of it like a game board, like Battleship or perhaps an Excel spreadsheet. I have a 9x9 view of this grid since it is not possible for a phone user to practically view 25x25, but 9x9 seems to work fine. And I have the concept of Current_Location on the map, which is dynamic and changes based on other factors. In TABLE1 I have records of all the "cells" in the 25x25 map that hold information about the cell (for example, the color of the cell). There is other information stored here that includes, for example, whether you can "see" other cells from this particular location in the grid…

For the life of me I don't know why I can't wrap my head around this.. Table A Parent Record Table B Child record that is a selection of an item of Table A (This is a BOM Setup) I want to "cascade" down and sum up all the values of each child record but if the child had more "Childs" I need those values too. Basically it can be infinite.

I'm working on an invoicing database just now which is, on the surface, relatively straightforward. There is a table of customers, a table of orders, and a table of order items. At the moment, when someone calls in with an order, the user looks up the customer, then creates a new order for them as a related record in the order table. That layout has a portal which allows the user to create line items for that order. When I've been chatting with users, the number one request has been some way to duplicate orders. Some customers order the same list of items over and over again and it's irritating to have to duplicate manually. (We have value lists etc. to save typing but…

I have been working with Filemaker for 1 month and want to set up a sort of 'agenda' for teachers. I had already made something that worked well but then came to the conclusion that my base was not okay so I can start again ... It's like this: A teacher teaches in multiple schools, each school has several departments. When the teacher opens the app (or logs in), he wants to assign students to one of his schools and then assign lessons to that student. The teaching moments differ from school to school. It is not the intention that he sees the lesson moments of school B when he adds and assigns students in School A. I mean by this: If school A has following lessons: "M…

What is the best way to choose a company name in a drop-down list in one field and have another field display that company's logo? I originally coded this process many years ago using a calculation field and series of "case" statements within the field definition. That worked fine when there were only a handful of choices. However, now that this list has expanded to 20+ companies, adding new companies and their logos to the list is extraordinarily cumbersome. The logos themselves are stored as Global fields in a related table. There are four separate fields within each customer's record where I need to do this. There has to be a better way to do this via a…

I'm creating a new database for our company. We have elected to begin with the FM Starting Point. It has most of the elements we need, however, one very important aspect of our business needs to be added. We are a vendor to the property management industry. Therefore, we have property management accounts and property accounts. Both are account records. We need the ability to relate the property account to the management account. A simple parent child self join should work for this. Add a portal and we can see all the property accounts that the management company has. However, we also need the ability to carry this relationship through the rest of the tables. We …

I have a table Exhibitions for photographic prints. Sometimes we only know the unique number of the Image in the print, other times we know the Image No. and the Print No., and some times we only know the Print No.. (Images have multiple prints, prints only come from a single Image.) We have a join table to connect Exhibitions with Prints and also with Images. There are two fields in our join table fk_PrintNo and fk_ImageNo. When there is a PrintNo. value in the portal row, the c_Thumb field of the join table gets the thumbnail from the Print record, and if there isn't a PrintID but there is an ImageID, the c_Thumb field gets the thumbnail from the Image record.…

I have a primary table 'Projects' connected to a child 'Tasks' table in a portal. In the child records, there is a check box labeled 'done' to mark off completed steps. In the Projects table, I also have a check box labeled 'Show to do'. When clicked, the idea is to update the portal with only those records that don't have the 'done' box checked. Unchecking would show all tasks again. I have experimented with all kinds of calculated key fields, but the way I see it, I cannot generate the proper key on the child side without looking at the status of the parent side. At that point, however, I can't index the field any longer and so it doesn't work. Any ideas? Than…

Hi all, lets say i have a invoice table and customer table i want to select the customer from a drop down list but I only want to see active customers in the drop down. How do I filter the drop down list?

Hi everyone, Not sure if this is even the right subcategory, feel free to hint. For several years, I continued to enhance our company database as we grow and collect data, invoices, articles, clients, companies. Now we opened a second company that I would like to integrate into the existing one. We want to share our contacts, companies and articles but not our invoices, bills and several other things. The database is build on the anchor buoy model, some selector connector methodology as well. Each user has a company code in his profile. In my ideal world depending on the user, his database connections would look at his own "invoices" or "b…

I have a table named Sales with the following data: ---------- ID DATE UNITS ----------- Based on Sample data below, i am trying to query the table by creating a relationship to find the ID that achieved a specified Unit amount on the earliest date. In other words, and for example, which ID achieved a specified Unit amount first (earliest date) Specified Unit Amt of 5 = 1 Expected records returned result 301 10/13/2018 6.5 Specified Unit Amt of 2 = 2 Expected records returned result 766 10/03/2018 2 360 10/03/…

Hi All, I have a database where the relationship between the Quotes table and the QuoteItems table is set up such that if a record is deleted in the Quotes table, the related records in the QuoteItems table are also deleted. I have now been shown an instance on one Customer where there are four Quotes missing from the Quotes table but all the QuoteItems are still in the QuoteItems table. How can this be done, I am unable to recreate a circumstance where I can delete a quote and leave the quoteitems in place. Any help appreciated

I have tried at least 3 different ways and it keeps failing. I'm giving up and asking for fmforum brains. Thanks in advance for helping to solve this pickle. 3 relevant tables: Visit, PI_Test, AllSpecies Table: Visit pk_visitID Child table: PI_Test fk_visitID fk_SpeciesID 1:1 table: AllSpecies pk_SpeciesID SpeciesName I need a summary count of all Species in the PI_Test for a single visit. Species Name, Species Count per this visit I need to be able to write the species name in a field, then the count for that visit in a field, ranked from most to least, excluding zeroes. This can be done in scripting only, it does not need…

I'm trying to summarize the amount of training hours for a specific department. I have 3 tables. Table1: List of people. Fields: Personnel Key, Person name, department Number Table2: List of Training. Fields: Date of training, number of hours, Personnel Key (from the Date, I get the Year and Month) Table3: List of Year/Month. This is where my calculations should display. I have a field for Month and Field for Year. Relationship #1: Table1::Personnel_Key = Table2::Personnel_Key Relationship #2: Table3::Year = Table2:Year, and Table3::Month = Table2::Month In Table3, I want to get a total of Table2::Number of Hours for Department 1. I…

Hi, being inspired by Matt Petrowsky’s YouTube video (Lesson #2: Scriptology Mastery Course FileMaker; BTW a great video series!) I added a "ValueList" table to my solution and it works just fine. However, I can’t make conditional value lists work. I'm lost! In the example that Matt gives, you set up a ValueList table containing all the category/value pairs of your whole solution and make a self joined relationships to that table for all value lists you would like to see. Keys are text constants that you define that hold the exact category names. Please see below the attached ValueList Table.jpg definition and the ValueList Values. In my solution I have a "…

Greets, all: I'm trying to reformat an existing report but what's holding me up is how to set up the schema (see attached)--as is, there are 12 fields assigned per Test Code (5002, 5003, 5004, and 5005) so there are 48 separate fields, each using E-SQL calculations, to generate the numbers for the report. (Mind you, all the data is in one table.) I figure FileMaker Pro can natively do it much easier--and with many fewer fields!--using summarizing functions but I'm scratching my brain trying to set up the schema. I'm thinking that each Test Code should have its own table--so I can keep the design columnar as-is--but what should the connections be? Copy the existing ta…

I have two sets of data: rankings (Name, Rank) and partner_selections (Name, Name_of_Partner if have one yet). I want for each person to calculate the best-ranked potential partner among people who do not already have partners (i.e. among records in which Name_of_Partner field does not contain a value, sort for the best associated ranking). I can do this by using a calculation field to hold the pairing_status (1 if have partner, 0 if don't), create a global field "Zero_placeholder" that holds the value of 0, and then do a self-join where names are not-equal and Zero_placeholder = pairing_status and sorting for the highest associated ranking. I would be inte…

Smart people, I have a hosted solution that runs a server side script every 5 minutes polling several different tables and putting the information on a Dashboard table that contains 1 record. I have a table that has 280,000+ records that needs access to the Dashboard's record. I made a Cartesian Join first starting with the Big table to the Dashboard table. After staring at 'Find in progress... Processing query' for an hour, I tried to start the Cartesian join from the Dashboard table to the Big table and have now been staring at the same 'Find in progress... Processing query' for over 2 hours. I assumed that since the Big table only needed access to the 1 recor…

Good evening. Could somebody please help me with this? Attached is a sample solution whereby I have two tables (Client and Activity) which are linked by a single field (Client_ID). I am trying to count the records in the second table (Activity) whose Programme (field) is "OR", "CC" or "C2" - as per the layout in the Client table. The solution attached is not complete, but I am unsure how to setup the relationship so that I can count these records. Currently I am only showing the total record count in Activity. Any suggestions would be appreciated. Thanks, Greg Sample.fmp12

I apologize but I searched up and down and did my fair share of research but I can't for the life of me figure this one out. So what I am trying to achieve is something straightforward: I have a main table, ORDERS, that simply has one record for each order. Amongst the fields in ORDERS, I have: ORDERS::Client and ORDERS::Agent. Both fields contain Text values; ORDERS::Agent being any one of a set Value list containing my 20 agents. ORDERS::Client is whatever the client entered; so the same client may enter his/her name ever so slightly differently. One time, they may enter Mike Miller; the next time around, they may enter M. …

Hi all, i've got a many to many relationship between my property and project. I've created the link table and the portal is showing the records that I want. I need to be able to assign more than one project to a property. What is the best way of doing this. I created a global table and a global field so I can set that and then use a script to update the link table. However, I@m not sure my relationship is good as I can't seem to get my global field to populate. Is there a better way of allowing this? Potentially I need to be able to assign up to 10 projects to a property.

I know I've done this before, but can't find an example of the functionality in my archives and can't seem to work out how to do it again. I've got two tables: PERSONS and CONNECTIONS. PERSONS has an `id` field and CONNECTIONS has `person_1_id` and `person_2_id`. There's a relationship from PERSONS to CONNECTIONS with `id` = `person_1_id` and from CONNECTIONS to another TO into PERSONS, connections_PERSONS, with `person_2_id` = `id`, but these relationships may need to change to make this work. On a layout with a context of PERSONS, I need a portal into CONNECTIONS that shows the names of the person on the other side of the relationship. So if Bill and Ted are …

Hello. It's been awhile since I was here last. Good to be back. I have some trouble in deciding what relationship setup to use, I am also not quite sure if I am trying to solve the issue I am having in the correct way so any input and advice is more than welcome, thank you. Setup: Standard invoicing solution for the invoicing of lessons with the following tables: Invoices, Customers, Line Items, Items and some others... Line Items table contains the following fields; Instructor_Name, Lesson_Time, Lesson_Date and some others Usage: Due to the nature of business, data is directly imported into the Line items table and a unique Line It…

I'm using a global field to filter a portal so that each user can set the global field and view different sets of records simultaniously. The problem I am having is that if any user clicks into any field, it is locking all records for all other users. I came across these threads that I think are related to my problem: http://fmforums.com/forum/topic/57413-relation-graph-cardinality-question/ http://forums.filemaker.com/posts/96fc4993b8 I think it may have something to do with having a global field on the RHS of the relationship. Has anyone else experienced this? Everything else is set up and working exactly how I would like, but the fact that all ot…

Ok, firstly not sure which section to put this in. I couldn't pick one so i started here. Admins please move it to an appropriate category if you wish. Anyhow, I was approached by a friend that asked me if this can be done in Filemaker. His plan is to have one master database of inventory that many different vendors will have access to but only to change their or add their own products. Then say in city one we have a store with a pos system (Filemaker). They will manage their own products in that store, but still have access to the master product catalogue if you will, to look up products that one or many vendors may sell. So the POS systems are separate from each ot…

I'm creating a simple database to keep track of a DVD collection, where the media are going to be removed from original packaging and stored in small paper sleeves. Each disc will get a record in the Media Table and the client wants to simply assign each Disc a unique sequential Location #. So: D-0001 D-0002 D-0003 etc. We would like to have an "Assign Location #" button that looks up the existing numbers and offers up the next available number in the sequence. To complicate matters a bit, we also want to plan for filling gaps that are created when obsolete DVD's are removed from the collection due to damage, obsolescence, etc. So say the highest number in the exi…

I'm sorry I'm pulling the level down to the basics, but I lack the basic knowledge of how a relational database works. My wish is to create a database of genealogical data that can be expanded as needed, but first I have to be able to decide which tables and fields are needed and then what relationships to create. The overall goal is to enter different archives and tie them together at the person level. Right now I'm entering marriages in 32 parishes and then census for the same parishes. Later, several archives will come to. The task is to identify the individuals and associate them across the databases. For example, if I want to find what information I ha…

Hello All. Just dipping my toes into FM and finding it opens many possibilities. Still must learn a lot to be proficient, so TIA for help. Background: I sign out molecular diagnostic cases. We have a compendium of tests run in the laboratory (all the tests that can be ordered, run and reported out) A Test (type) can be run on many patients. A test (instance) has one patient and each test/report/result has a patient (one). Each patient can have >1 tests. There are a set number of reporting options per test (TestA results can be Positive, Negative, Suspicious, etc (options are in a value list) Each report…

I am not sure if I am posting in the correct location. I have been working on a database for a few years now, I will not call myself an expert, but I have figured out how to do a lot of things. I am at a spot where I need help. I have created custom invoices and status screens for my screen printing and embroidery company, now I need to create a customer referral program. I have already set up the relationships where I linked the referrer to the referred. I can see totals of each referred customer in the referrer's customer layout. I need a way to add the points, which is 1 point per $1 spent, to the points section of the customer la…

Hi FM Mavens, this will be long, I am sorry. The following is the setup: Small medical office database I am writing for myself. 4 tables: "Pts", "Encs", "Rxs/RxDup" (patients, encounters, prescriptions - patients have several encounters, encounters have several prescriptions.) RxDup is a duplicate of Rxs but that is not why it is called RxDup, it is called that because it is used to duplicate individual medication orders. Mac OS 10.13.2, FMPro Advanced 15.0.4.400 Task: (a) selecting an arbitrary number of records in a portal within Pts that looks at Rxs by setting the match field to a certain value (b) going to the records that become related via this act …

I am not sure if I posted this in the correct place, so please forgive me if not. I have: FM16 Pro Advanced (Have both Mac and Windows, but I use a Mac to develop), FM16 Server. I am trying to have a drop down value list populate with information from two different tables. Let me explain: I have a table called Products. this table contains information required for an individual product that we sell. Some of the fields inside the products table are: ID.pk, name, cost, price, etc.. I have a second table called Bundles. This table contains multiple items from the products table that become a single sellable bundled item. Some of the fields inside the…

Hi, I have no idea how I can make something like versioning of a field. I have 2 tables. The first one with products related to tabled with prices. I'd like to store every change of a price in the second one.

Hi Everyone, I have my working solution with 10k+ records within one of the tables field i want to create new table, How can i create new table from already existing field. To clear my point i have made a demo/sample file. I have a TableA with ID and two fields Person Name and Office Name. How can I make new TableB using field Office Name from TableA and keep the existing records. OR How can i split TableA into TableB using field Office Name and give it ID/auto enter serial. Thanks in Advance for any Help. split_table.fmp12

The Problem: I have a filterable portal (1) displaying contact names from a staff table. I need to click on a contact to add them to a separate portal of invitees (portal 2) on the same layout (Events) . I then need the original portal to either hide (or in some other way denote) the people already added to the second portal. In other words, it needs to be clear that a person already invited can't be invited again. The layout is from the table: Events Portal 1 is showing records from Staff (filterable via a global search field) Portal 2 is showing records from EventInvitees I need Portal 1 to only filter through those staff who haven't been invit…

I am working on a relationship with a scheduling database. There is a MasterTable with all the events and each have a category. My DateAndCategoryTable create a one line summary for records that match the day and category. The SummaryOutput is like Category A: 7:00a - 9:00p: Talk 1, Talk 2, Talk 3 My SummariesForDate table creates a list of all the summaries for records that match a date. The TextSummary is like Category A: 7:00a - 9:00p: Talk 1, Talk 2, Talk 3 Category B: 7:30a - 5:00p: Talk 4, Talk 5 Category C: 8:00a - 3:00p: Talk 6, Talk 7 I am trying to access that TextSummary in my MasterTable so I can display in an …

Hello , i'm still new with FM , trying to find my way to make my own Database that help us for our maintenance job. I made a hierarchical Value list start with Selecting "Area " , then " Equipment Category " then "Equipment" . The problem which i have . i'm not able to select different choose in the "equipment" list , Although it show all of my equipment list , but always one equipment is selected in each Equipment Category . For example : Area 1 ,Equipment Category 3, have 4 Equipment . . . . always equipment 3 is selected although the 4 is shown . Any Idea what's wrong here .??

Hey there, I'm self taught and hoping someone can help me with this seemingly complicated relationship. I'm essentially trying to build a layout that lets you link a "Tradesman" to a "Job" I have a tables, "Job", "Tradesman" and "Job_Tradesman" "Job_Tradesman" being the table that links the two, since 1 single tradesman can be working on multiple jobs and a single job can have multiple tradesman. I have managed to build a portal that will show the tradesman linked to a job, however I now need to display the tradesman NOT linked to that job in a different portal. My idea being that I will make buttons that the user will click the "Tradesman" within the …

I need some help with my relationships to display the correct information in a portal. I setup this test file for anyone who is willing to take a look at it for me. This test file is setup with dummy restaurant data to make more sense. I have a ::Products table where every item that we sell will ultimately be kept. There are items like pickles, mayo, hamburger bun, meat patty, plastic cups, etc. Some of these items are sold to the public (i.e. Apple Pie, Packet of Ranch Sauce, or an extra meat patty). Others are not sold as separate items but will be used to make up a bundle. Next table is ::Bundles. It only has three fields BundleName, Qty, and AssociatedP…

Using FMP Advanced 16. Having an issue figuring how to achieve something. I have three tables: 1. Stores - contains information on each store location (Store_ID, store name, location, sales volume) 2. Zip_Info - List of zip codes with fields for number of people in each zip code, plus a field to indicate this zip code is "targeted' 3. Zip_distance - list of each zip code and how far it is from each store_ID in table #1 #1 is related to #3 by "Store_ID" #2 is related to #3 by "Zip" The problem is that in table 3, there are multiple instances of zip (between 2 and 4, depending on the store). Not every zip has a listing for more than one store in ta…

Hi everyone good afternoon!! I’m dueling with FileMaker trying to make the following: I have 3 tables (this is for a game I’m developing): Player (id, name) PlayerStats (id, playerId, statId, value) Stats (id, name) The Player relates to PlayerStats with one to many. The Stats doesn’t relate to anything as I’m using it more like a list source for the “statId” field (or should I make a relation there?) my question is how can I make it so that when I add a new player record it automatically creates all the PlayerStats entries (on for each entry in the Stats table for that Player) and when deleting a record from Stats table all the PlayerSt…

Hi everyone I’m quite new to FileMaker and though I don’t think this is a FileMaker related problem I don’t know how I can accomplish this: i have three tables: Actors, Classes and Traits. each Actor has a Class (til this point everything is okay) and both Classes and Actors can have multiple Traits (and that is my problem!) i don’t know how to make the relationships between this tables. Because both class and actors table need to reference the Trait’s one. I though of adding a hidden field in the traits table that could differentiate from actor or class... value=1 actor... value=2 class... this way an identical foreign key value could represent a clas…

I have represented a bug in a simple exercise: a field "previous_serial_number" in a table looks up the auto-entered serial number from the just-previous record in the table. Record 2 should have 1 in the previous_serial_number field, for example. Something is not working: record 2 has 2 (it's own serial number) in the previous_serial_number_field. I'm doing a self-join, with the match field being the serial number and the relationship being not-equal, sorting the matching records on the serial number in descending order. Record 2 is not supposed to be matching with itself, but it is. New records are listing their own serial numbers in the previous_serial_numbe…

I am building a new system ground up. There are three section ; - quote - docket - invoice In each section there are three relationships going to the table partner ; - production (company) - agency - bill to table : partner fields: id, id_company, company, address, phone, contact, email quote turns into a docket and docket will then have related quotes and invoices. Once the partners are established in the initial quote, they are unlikely to change in all three section for the duration of the project. Regarding keys, this is what I am thinking and I am just looking for a 'brain check'; My thought is I want to maintain/relate th…

I have a database where I have created a global field where when I paste in a list of values it shows me the matching fields via a portal. The global field (listfind) matches a name field (design) via a relationship with an additional occurrence (designdata_match) of the main table (designdata). That is working fine, however what I want to add in is a way to indicate if one of the values I paste into the global field is not matching in the other table. It could be as simple as highlighting the value in question in a different color. I am unsure how to achieve that. Any help would be amazing.

I have a table of Transactions that are grouped by Batch Number, for several Types of transactions. I want to create a portal that contains only the Batch Numbers of Open Transactions, and I need the portal to contain each Open Batch Number only once. See the attachment for a representation of all transactions, and the desired portal view. I'm looking for guidance on how accomplish this using data relationships, if possible, so that the portal will show the currently Open Batch Numbers whenever a Batch is changed from Open to Closed, or new Open Transactions are added. Thank you. …

I hope i get answers to my questions. I only have two questions: Is my database ERD correct? if i want to display info about the tenant living in certain property, his payments and services done for that same property, how do i do that? i used rent_records as joint table, tried portal and its only showing the startdate and enddate from the rentrecord table.

I have the following: Table 1 – Expenses Field A: Date Field B: Concept Field C: Amount Table 2 – Flight Time Field D: Date Field E: Flight Time (in hours) I need a report that will give me the expense cost per hour, i.e. the total of Field C divided by the total of Field E. Table 1 and Table 2 are not related. What relationship must I establish between them to obtain my desired result? If establishing a relationship between the tables is not the answer, how else can I achieve the figure I need? Thank you for any help that can be provided.

New to FM. Please tell me if the relationships are correct? am i missing any table? missing any fields? Thanks a lot.

Hello, New to Filemaker and new to this forum. Hoping someone can help me out. I am working on a personnel planning system with, among others, the tables Employees, Shifts and Availability. tblEmployees has fields like Employee ID tblShifts has the fields Employee ID, Start Timestamp, End Timestamp tblAvailability has the fields Employee ID, Start Timestamp, End Timestamp and Status When in the layout Shifts I want to open a portal in a popup and I want this portal to be sorted by Availability and I want the portal rows with employees to be 'greyed out' when status = 0, green when status = 1 and normal when no Availability is given. I basical…

I’m attaching a cluster of screenshots that document a join table. All field are indexed. It looks correct to me, but I’m not able to enter groups in the portal. I can’t even access the field. Any help would be appreciated. Thanks. Join.zip

Hello guys I am really struggling to get this job done which seems pretty easy. I have two tables, let's call them Table A and Table B. On table A I have records of the product flow into the production chain (product goes from phase 1 to phase 2 and so on). On table B I have records which tracks the movements through export from Table A. So table A will give me the situation on every department of the chain production, while Table B will give me totals based on day or hours or month etc. I have created a third table, Table C, on which I want to get a day by day situation from the above tables. On table C I would want to get the summary from Table A and…

Hello all - I think this is more complex than it seems. I have a table of rooms with variations criteria to describe it. Actually it is a lot of rooms (a university). I would like the user to be able to save a suite of rooms ideally with a flag field so a further search is by the flag only. This found set would be a "project" where the data can be analysed. Where it is getting difficult is the ability to have multiple projects, each with a Name for reference and reporting. Ideally I would like a portal which shows the full list of projects projects - say up to 10 options and a check box which acts as the search flag. My thoughts are I need three tables - ROOM Table&g…

Hello I have 2 DB with the following fields VIDEO: CLIPNAME, RECORDED_DATE, and other fields not relevant to my question AUDIO: SOUNDFILE, RECORDED_DATE etc Unfortunately the recorded dates are different and so are the names. I need to link the two db and I hope to find a way to relate the two files so that the RECORDED_DATE in the VIDEO file will tell me what is the SOUNDFILE recorded in the other DB I tried to add a STARTDATE and ENDDATE to the AUDIO db to get a range of times so the VIDEO file could give me the Video FileNames recorded during the selected timestamp range Here I add samples of the two files to make it easier f…

Hi, I'm not sure if this is the correct place to ask this. But I have a issue. Basically, I have a layout which shows an Estimate. Each Estimate layout has a related table for (main estimate, descriptive, other). Each of those related tables has their own fields with detail and pricing. I have those as portals on the Estimates main page. My client needs to print out an invoice that shows all the estimates from the related tables in a line item type of view. I was thinking to create a table with all global fields. The script will loop through each related table and transfer that to the new global table by adding new records. Then it will loop through next …

Hi all Using the Geist Database transactions method I am restoring stock levels of a single stock line, and then removing the stock ID from the Line Item (which is the parent record). The idea is that I am replenishing the stock, it being no longer assigned to the Line Item. I am not using a portal, it is just a single related record ('Recurring_Services_Stock'), and the editing of the primary record ('Recurring_Services') must occur after the editing of the stock level (otherwise there is no relationship). For these reasons I believe my script is slightly different to examples Todd gives. Therefore I'm interested to know whether my script is excessive, or insuf…