Relationships

I’m relatively new to Filemaker and developing a solution to track trademarks. I’m deciding how to structure the data, and would really appreciate any pointers. The abstract data structure is this. There are owners. Each owner can have many trademarks. Each trademark can have many registrations. Each registration can have many events, many of which involve deadlines. Some events are initiated by the owner, others by a government entity, others by third parties. Some events may be superseded partly or entirely by later events, such as when an application is amended. The purpose of the solution is to track the registrations, their status, and …

Okay, I am working on an Inventory Solution. Right now I am just focusing on the Data Entry end. I have the following tables and relationships: Assets Table AssetID_Pk ItemID_Fk ManufacturerID_Fk CategoryID_FK Item Table ItemID_Pk ManufacturerID_Fk CategoryID_Fk Manufacturer Table ManufactuereID_Pk Category Table CategoryID_Pk Each AssetID can only relate to one ItemID, but one ItemID can relate to multiple AssetIDs Each ItemID can only relate to one ManufacturerID, but one ManufacturerID can relate to multiple ItemIDs Each ItemID can only relate to one CategoryID, but one CategoryID can …

summary of problem - any thoughts on really fast validation attempts to see if child records exist? I have a parent table (called SUMMARY) that has records that store values from a child table (called TRANSACTIONS). The purpose of the parent table is to store summaries of records from the child table, so instead of the user waiting for an unstored calculation, they can access the parent table's stored value. An example of this is ... we might store the total for all sales for one month. so the child table may have 10,000 'sales' records in the month, while the parent table has one record with a number field (stored) showing the sales for that month. …

Hi can anyone advise the easiest way to have filemaker report the number text fields chosen via a drop down field. I have a drop down field choice of "test1" "test2" "test3" If i enter 3 of each choice in my records I can successfully get the total summary to show me "9" entries. What I want to try and do is get filemaker to show me "test1" was selected 3 times "test2" was selected 3 times "test3" was selected 3 times. Many Thanks for any help offered.

I hope someone can offer some very basic advice as I worked on this problem for many hours and think I am misunderstanding some fundamentals about relationships. I have a contact management database and the area I need guidance is with my ToDo list - All I want to have a layout which will only show "OPEN" tasks and exclude any that are defined as "CLOSED" based on a field titled STATUS. A pretty fundimental and straight forward requirement for any business, when I meet with my colleugues I need to click a button and see the tasks we need to focu on and not the entire list since the start of the database I thought about just having a script perform a find but …

First-time setting up a complex (or what I think is complex) database, and really could use some help. The idea is to be able to create an inventory of Computers, Peripherals, Software, and Software Licenses, (and where each is installed/located), along with the Purchase Order information for each item, and the Vendor for each P.O. Here's how I sort of see it breaking down, but I'm confused as to how to setup the tables and relationships: One Computer can have many Purchase Orders associated with it. One Computer can have many Vendors/Manufacturers/Sales Reps associated with it. One Computer can have many pieces of Software installed on it …

I have a database (admittedly a frivolous one) that tracks my rating (1-5 stars) for Magic decks I have built. It also tracks how decks have performed. In one table, I have created threshold values for five groups, based on games won of those played: those ≤20%, those >20% and ≤40%, those >40% and ≤60%, those >60% and ≤80%, those >80%. I then want to look at the degree of correlation between these two values (star rating and winning percent), which I do based on a minimum number of games played. First of all, what I have works fine, even when I drop my threshold to one game played and bring up 378 related records. The time to get everything pulled and cal…

Folks been scratching my head on this for an afternoon and was hoping I could pick your brains. I've tables invoices & line items (one to many relationship) and products and materials (also one to many). Each product can have multiple materials and each invoice multiple line items. I'd like to be able to open up a bill of materials for each line item and have various calculations based on the total number of items on the line items and the weight/quantity of the various materials. So far I can't seem to get this to behave properly - the line items total quantity doesn't seem to tie in with the materials section and thus calculations come out all over…

Hi, In an Asset managment solution, do I absolutely need these tables: Asset (e.g. HP Photosmart 102b)------< Individual Assets (e.g. HP Photosmart 102b | s/n:1234567890)---------< Transaction Items >------------Transactions ? Currently I don't have an Individual Assets table and all Individual Assets are created in Transaction Items table and a Serial number is assigned to them there. When I sign out an Asset, I create a new Transaction and when it is returned, I flag the Transaction as VOID. I know I have that Asset at hand if it appears only ONCE in my CURRENT Transactions. (VOID Transactions are excluded via self join) …

Hello, I am extremely new to filemaker and am kind of learning as I go. I am trying to create some total count fields based off of certain criteria but I cannot figure out how to do so. My database is made up of members and practices, each from its own table with relationships connecting the two. In each table I have the province the record is from - for example John Doe is located in Alberta. I want to create a dashboard type screen where I can total the number of active members per province and total number of active practices per province. As members or/and practices are added or removed the calculation should adjust accordingly. And active member/practice i…

I've been trying to create a quick demo file for a proof of concept. I've got 2 Tables.. Users pkid (serial Number) UserID (serial Number) Name Company pkid (serial Number) CompanyID (serial Number) Name My end goal is having a portal in both Users and Company layout that will show all the users assigned to a company or all company assigned to a User and to allow to add/delete from the portal. I know I'm missing a key part in how to relate them to one another? What am I missing?

I think I have a relationship problem here even though the problem appears to be with the Portal or Popover. As you can see from Screenshot1, the popover is showing the data for the wrong record. Interestingly, clicking the Go to Full Record button which calls Go To Related Record does go to the correct record as indicated in the Portal. This is also happening with the Siblings Portal, see Screenshot2. However, the Go to Full Record button on siblings portal behaving differently - it is going to the record showing in the popover which is the same record being display. So it goes nowhere of course. The Relations screenshot shows the relevant relationships as…

Hello, I am beginner with filemaker and I got a database from a collegue. In the database we create reports and the report number serves as the link to the different tables. The report number is a consecutive number. Now I have the problem, that I created the reports not in a chronological manner because I did not have all data available and thought I could adjust the report number afterwards. But when I change the number all fields are set on blank again as they are linked to other tables over the Report number. Is it somehow possible to rescue the database or do I have to start over again in the right chronological order? Thank you

Hi there! I'm new here and almost new to FMpro (such a great program!). I'm from Belgium and I actually live in Brazil, so, please forgive my english if I make some mistake. I'll try to explain my problem clearly: let's consider a Cake factory. I got those tables: Cakes (identify the cake and its specificities such as flavour ...) CakeDetail (Join Table between cakes and ingredients - a reciep-like table) Ingredients (table with items such as floor, eggs, fruits...) Production (this table is filled with everyday production - ex. 3 chocolate cakes, 4 cherry cakes....) ProductionDetail (Join Table between Production and Cakes where we…

Hi Guys, So before making my big Database (and before purchasing V14) I am starting to put together with pen and paper various tables I would like to have. First of many questions! So, for example, I will have an invoice table, and things such as the customer, amount(s), dates etc will be stored. As a connected item to my Invoice Table I'd like to have a budgetter within the Database. Basically, what we do is file certain 'types' of expenses, with a total statement per job. So I can see Invoice 123 and find out what expenses were related to that job. I've uploaded an example of how we do it in an Excel format currently. I'm looking for some point…

Hi Guys, So I'm having a bit of a disaster. I've made a really basis opening Database. I've managed to get Drop Downs to connect my People to my Company/Clients. Easy! Where I'm having the next issue is in my invoices Layout. I basically want select the company and have the program fill in the address, but it won't. I've looked for related tabes, but it says there are none? I'm a bit confused, as I've related my ID in my Clients Table to my IDFK in my Invoices Table. I can see a relationship between the tables - but it's not working! Can anyone help where I've gone wrong? Thanks! Database.fmp12

Hi all I've attached an image to illustrate my issue. I have a sorted relationship between Email Script_Originals and Email_Script_prepopulate_Originals via an email address and 'unassigned' field. This works fine. However, the additional relationship (Email_Script_prepopulate_Cases) that comes off the Email_Script_prepopulate_Originals TO is not working correctly. As the image shows, it pulls in an unrelated record. The Case_ID and Case_IDF fields should be exactly the same. The record it pulls in IS a related one, but only according to the first relationship IF it didn't have a sort specified. Now I've just managed to resolve this by applying the sa…

I am having a very similar problem to this post: I have three tables in a standard many-many relation ship, Parents and Students and Parents_Students. I want a portal on the Parents layout that will show the other Parents related via the Students table. One way would be to put a Spouse field in the Parents table for a more traditional self relationship, but I might have more than 2 "Parents" the others being grandparents, aunts and the like. Hence the many-many self-relationship. The answer in the post above refers to a broken link (probably from the earlier version of the forum software) and I cannot find the new link for new post. Could someone refe…

Hi everyone, [Background: Working with FileMaker Pro 14. Self taught via Lynda.com tutorials and FileMaker Pro for Dummies.] I am trying to build a simple solution for a new PhD program at my local university. I am running into an issue regarding the make up of my ERD table. Here is my problem: A single student will have two advisors. A single advisor may advise more than one student (a clear many-to-many relationship). However, I do not know how to define my third table without creating a one-to-one relationship between students and this third table. Do anyone have any advice? Thank you in advance.

Hi Forum I'm in the process of creating a service module for our customer database. The products requiring service can be either hardware or software, and I have them as separate tables. My idea was, that when creating a service sheet for a product in the service table, selection for the product could be done from a single popup lookup value list. The value list would show all products, both hardware and software, related to a customer. I'm totally open to good ideas and suggestions, as I'm stuck here :-) I've attached a simple sample file which hopefully shows what I'm trying to achieve. Cheers Peter Service_Test.fmp12

I need your advice on table relationships setup. I am trying to create stock trading simulator, and I started with STOCK table, where all the stocks are placed. then I created DATA table, where all the stock data is stored (date, symbol, open, high, low, close prices and volume). and those tables are related: STOCK::symbol = DATA::symbol. But as for simulator, I need 3 additional tables: - ACCOUNTS [one account can have many orders] - ORDERS [one order can have many line items] - LINE_ITEMS So, basically, you start with account (let's say, record is 'Virtual account'), you then enter order (let's say, order is 'Covered call'), and that order in line_i…

Hi, i have a Filemaker Database with a TableA-fm. This Filemaker Database is hosted on an FMS14-Server, which is configured to use the MySQL-Odbc Driver to connect to an MySQL Server. The Mysql have the same Database and Table structure as the FM have. (TableA-mysql) now i want to do the following: Create a layout in FM on TableA-fm which let me enter Data (via Fields) to TableA-fm and simultaneously the same data to TableA-mysql on the MySQL Server. So i would like to have the same records of data in the FM-Table and the MySQL-Table. Any ideas how to do that automatically, or sync TableA-fm to TableA-mysql?

Not sure if this is the right forum, but I think it's something basic to do. I want to have an option in my assets layout where the user has the possibility to upload a PDF and attach it to that asset. I understand it is with a container field, but how do you make the container so a user can always upload files with no amount of files as a limit. An asset can have many files uploaded to it, so how could I make this? thanks for your help

Hello, In my solution, I have a "Personell" table which includes about 400 records. Personell is distinguished, according to its specialty to : mechanic electrician driver Some fields in this table hold attributes that are common to all records(Personell) like: DOB Address Phone number Email et.c. On the other hand, each specialty has its own unique attributes. For example "drivers" have: licence ID licence type Total hours behind wheel et.c. Should I put the fields that hold specialty-unique attributes, to my main table ? OR Put them on a related table that holds onl…

Hi there, I have these two tables, Projects and Clients, which are related. Now, I want to be able to see client info such as ClientPhone, ClientFax and ClientEmail in my Projects layout (bottom part in grey) when I select that specific client (ClientName) from a value list that gets its values from the ClientName field in the Clients Table. Don't know how to do that. Can anyone help? I am attaching the DB. Thanks. NR Test.fmp12

Hi...new to the forum...thanks! I am rather new to FM but have created solutions before...I am embarking the creation of the Psychotherapist's patient database and right off the bat I am stumped. To keep it simple, let's say I have a Patient Table, an Invoice Table, and a Sessions Table...however, my patient table will include both INDIVIDUALS and COUPLES...a couple consists of two people, with unique information for each person, name, phone, email, siblings, parents, etc. However, they will still function within the database as ONE unit...meaning they couple will receive many invoices (addressed to the joint address, or a specified email address, or both email addresses)…

I'm creating a database to keep track of our IT dept servers. It's similar to a Customer > Invoices > Invoice type setup. The 'server' table is the main database where each server gets entered. Because each server can have many 'MAC addresses', 'IP addresses', and 'domain names', I've created a table for each. It's working except I'd like to have a portal show ALL domain names for a single server record that's being looked at. I've tried creating a new table occurrence for "domain_names", as shown in the image, and linked it to both: • domain_names__server::server_addresses_fk X server::ID …

Hi Everyone, I’ve been building a database over the last several years and recently discovered that I am missing a few entities in it’s structure. The database is designed to help organize post-production for film and television. It was only as I got further along down the road that I realized there were elements I was missing. I wanted to do certain things and discovered that with the way it’s currently built, I couldn’t. I’m going back now to re-think the structure of the database, adding in new tables to give me the functionality I need (and may need in the future). Going back to basics has brought up a couple questions: 1) What criteria do you…

HI all, hoping somebody can offer some advice on Filemaker Advanced 14 I have a self-join table, so two occurrences of the same table, which have customer names in a field which I need to compare. The problem I have is that when the customer names have spaces in them the join fails if the source value is just one word of the destination values. For example if I have a customer name of "Fred" and I want to setup the relationship for that, I get matches on "Fred Flintstone", "Fred Bloggs" and "Fred" when using the GoToRelatedRecord script step. Normally in a find I simply use the == operator which is all good but as it's a relationship I don't have that …

Basic database Customer --< Location ---< Invoices UID keys to foreign keys pretty typical... However the billing company is changing for many locations - it was a distributorship that is winding down and each location are going to be billed directly.. To preserve historical data. one thought would be to ad a multi predicate between locations and Invoices so that oldCustomer / location invoices remain linked and when the new customer UID is assigned to the location only new invoices are connected. other though was to add GetContainerAttribute ( List ( customer_UID ; location_id) ; "md5" ) to the location table and use …

Hi i have a manufacturing solution which goes as follow. every product has many ingredients. every ingredient can be in many products. the ingredients get selcted to a product through a portal with drop downs. unfortanately i can`t seem to display the products which relate to the ingredients. where am i going wrong, how would the relationships be different thant the one to many i have many time already in my solution. please could someone help me out , i would greatly appreciate it.

I have a parent child relationship defined in my database but there is not always a child for every parent. I would like to display ALL Parents and child records when they exist in a single portal.

Hi, I would like some advise on how to setup a database containing a (biology) taxonomy. I'll explain the idea. I will have a main table with information about a species and the taxonomy linked to this information. It's the taxonomy part that I'm not sure about what is the best way op setting it up. It contain species, genus, family and so on. (About 10 rank levels) After searching on the web I have the following ideas. 1. I make one table containing: ID, name, rank, parentID Then creating 10 (for each taxon rank) table occurrences relating parentID to ID. 2. Create 10 tables, one for each taxonomy rank with: ID, name, parentID …

Apologies if this is in the wrong forum, new to FileMaker and unsure of how to articulate what I'm trying to achieve. I am trying to reorganize a database using FileMaker. My client's work is literary, and wants to be able to sort agents/editors by their publishing house/imprint. I am using the Personnel Files starter solution, as it comes with some of the features I'm looking for, particularly the "Personnel List". A quick summary of relationships: Publishing Houses [House] are parent companies. Sometimes they do their own publishing, but it's often left to children companies, or Imprints. There are House-specific publishers, editors, etc [People]. Imprint…

Hello, I have two tables: Companies and dupcheck_companies...Self-joined with Companies:company = dupcheck_companies::company AND Companies:pk != dupcheck_companies::pk And attached script trigger with layout (onrecordcommit) to show custom dialog if duplicate key (serial of table occurrence not empty) is found .. it works fine when duplicate company name value is found , but what if I want to check another field (phone number) for duplicates , I think it would be better if I could self-join with an OR condition instead of AND , but don't know how Please help!

I've been thinking about this problem for some time now and I can't seen to wrap my head around a solution. I have a standard employee database that of course keeps employee data and in a related table "comments" about that employee. When I put an email out to all my employees I add that as a record in the comments table. There is a many-to-many relationship between "employees" and "comments" because each employee could have many comments and each single comment could apply (potentially) to more than one employee. The comments table has a "category" field which I use to detail whether its a public comment ("hey...everybody clean the coffee pot after your done") or a more …

I have a db with two tables: 1) Company_info 2) Contact_info. I would like the ability to create new contacts while in the Company_info table. I set up a popover button in the Company_info table to do that, but it only creates a new record in the Contact_info table without any related data. When using it to create a new record in the Contact_info table I can't figure out how to make it copy over the "Company_name" field to the new record in the Contact_info table. The two tables were set up with a one to many relationship connecting the two IDs. (The first ID value is "Comp0001" for the Company table and "Con0001" for the Contacts table). All attempts to use "In…

I have successfully built a one to many relationship between two tables called Orders & Stations, with a 3rd table called order_stationlink where an order may sometimes have more than one station on the order at a time. Now, enter a fourth table called PSA's. It's separate set of data that is processed a little differently - and that will be brought into the main Orders table later. But instead of a field called Station, it is a field called Region, which may include one or many stations at a time. What I'd like to do is be able to pull in all the Stations that belong to that Region, and somehow move that data over into the Order once the PSA is ready for sub…

I am having trouble pulling date from an external filemaker file. One file contains a client profile table, the other a client notes table. I have set up a basic relationship in both files (ClientIDClientProfile is equal to ClientIDNotes) My calculations are working getting data from the Client profile to my notes file but not the other way around. I thought I have set everything up the same both ways... what are the first things I should check when I cant pull data from an external filemaker file using a calculation? Im using filemaker 12 pro, any help would be greatly appreciated!

Hi Everyone! I am very new to file maker, I am using file maker 14. I am trying to design a friend a database to manage hires. My goal is to bring a stock level back when inputting on a sales order form; Here is the scenario You have inputted customer details and event details You then input a sales order which looks at the date of the event and the item, checks the db for any matches, if there is it will bring a stock figure back. For example I have 100 white chairs to hire and the data of the event is 16/07/16, we have 350 in stock. as soon as I input it I want it to take away 100 from the stock but ONLY for that day as they will be collec…

So I have a Person table. There are two types of people, the one who is filling out the report and the person who is the subject of the report. If at all possible, I'd like to be able to pull both of their records from another table using their primary keys. I can't think of how to do this though and may have to resign myself to separating the persons table.

Hello friends! I am building an application for a flight instructor who wants to keep track of the organisation flights. The database has a table named "PILOTS" (one) and a table named "FLIGHTS". The problem i face is as follows: Any flight has a crew consisted of CAPTAIN and CO-PILOT. Any pilot for "PILOTS" table can sit in any of the two positions depending on the certain flight. How do i set a relationship, so that I can see all the flights from the perspective of one pilot (basically i want the matchfield to be FLIGHTS::CAPTAIN or FLIGHTS::CO-PILOT) Thanks!

I have two tables, well say Table 1 and Table 2. Table 1 has Field A and Field B, Table 2 also has Field A, which is used to relate to Table 1. On Table 2, I can have a portal with Field B in it (from Table 1) but the problem in this case is it would be very helpful to be able to see several portals at once. But Field B only contains 1 or 2 digit numbers. So what I was wanting to do in the layout for Table 2 is have Field A and well say Field C, and just view as a list. Field C would have all of the related values from Table 1 that match. So the data would look something like this: 2 17 5 14 32 8 In other words, Field C is taking the place of the portal, …

I have an old solution that started out in FileMaker 5 with 35 files and I am working to consolidate it down to 5 files. The user interface will be two files and the data will be three files (general data, more sensitive data, & images/containers data). There are currently 106 tables in the general data file and quite a few less in the other two files. 1) Is it unwise to have so many tables in one file if there should be data corruption that requires recovery? 2) I am re-creating the field definitions and just noticed after duplicating a table occurrence that the name does not appear in "Occurrences in Graph". I have tried re-doing the action, opening &…

Hello, I'm very new to this program and need some assistance in creating a database. I've been watching Guy Steven's YouTube videos and I've learned as much as I could. I'm trying to create a "table" (where I can list specific medications) and a "checklist" (descriptions with "completed" buttons on each end of each row) in a section of my layout where I've created multiple tabs. I'd like to create a table under "Medications" and a checklist under "Checklist". Would anybody be so kind to give me some pointers? I've never worked with databases before so I'm learning everything on my own via the internet. It'd greatly be appreciated. Thanks in advance, Bria…

I use a multiple table (formerly multiple file) structure to track work on a matter. For years I have archived that matter by "exporting" the data into the file via a number of lookups to pull the data from multiple tables into the archive file. This stored the data in a static format since we wanted it to show accurate information at the time of archiving. That data would not change. I would then delete the matter from the active matter database. If we were done with our client (all their matters were completed) we would delete that person as well. After reviewing comments in this forum it appears what I should have been doing was marking the matter as "completed" and th…

So I have two tables. I'm going to be very specific and use very general terms to make sure there isn't any confusion, so thanks ahead of time for bearing with me since this description might seem a bit monotonous. Table A Name_fk Item Number | Table B Name_pk Team I put a relationship between Name_fk and Name_pk. Name_pk is a list of names and what team they are. Name_pk is filled with entirely unique values, so you know John Smith is on Team Blue there is only one record with "John Smith" in the Name_pk field and that record has "Team Blue" in the Team field. Now Table A will have a lot of repeat items under Name_fk. There will be ten rows with…

I have two tables Claims and Premium Records on both tables have dates I want to do an analysis for both tables on monthly and yearly basis with a calculation field Analysis (Claims::claims + Premium::premium) my objective is to be able to gather all the records for each month and each year on both tables using that analysis. That is my basic task. There are other fields like Remark, and recommendation but they are based from the result I get from the Analysis. For example, Analysis field is > 120 , Remark shows Excellent.. please how do I achieve this?

The situation - I have 2 tables: Tasks, Dates Tasks has fields: id, dateNext, zz_1 (zz_1 is a global constant = 1) Dates has fields: id, id_Task, date, complete Date is related to Tasks via: id_Task = id and zz_1 NOT complete The relationship is sorted by date So, all Dates that have not been marked as complete will show in a portal the Dates records in the correct order. My question is - I want dateNext in Tasks to be automatically updated when a record in Dates is changed. In particular, when a record in Dates is marked as 'complete' I want the nextDate in Tasks to update with the next value in the portal.. if no record e…

Is there a limit to how many fields a table should have? My solution requires the management of 6 categories. Each category has up to 34 sub-categories, although most have 9-12, and each sub-category has several items requiring data entry. This leads to 91 sub-categories and hundreds of fields requiring data entry. The records per sub-category require relationships to corresponding sub-categories that respond to the data recorded. It is like a question/answer system, so the initial question field has data entered that requires answers entered and/or available (from previous experience) in the corresponding answer table. I have been thinking that I need to have the 91…

The attached files the replicate of the file I have problem with. It works fine in the local computer when I search for product line name. However, in my server when I try to find product line name. It produces "...one or more of the relationships between these tables are invalid" Is it because of the files in the server? fmdata2.zip

In my attachment, when I search for "Cutting tools" in the main table. It produces the following message. Is there a way I can get rid of this message. I think I know why because I got my product line through calculation of look up. "This operation cannot be performed because one or more of the relationships between these tables are invalid. " fmdata.fp7 fmdata.fp7 newfmdata.fp7

Hey there! I hope I put this topic in the right place. I am quite stuck on something so I thought I would crowd-source some input. I'm working on a Workflow database that also includes Inventory, Account Management, and Estimate functionality. My question pertains to one specific aspect of the Workflow part of the database. The database currently has Events which then can have multiple Work Orders, the Work Orders can have multiple Projects and the Projects can have multiple Tasks. Tasks can then have multiple parts associated with them. I am trying to develop a part of this database that has the capabilities to store "static" data at the Project level and below…

I am setting up a sustainability assessment database and am new to FMP, and although I have worked through many of the main basic requirements, I seem to be stuck on how to connect one record in the 'clients' table and one in the 'indicators' table so that the join table, called 'client indicators' will show which clients have had which indicators assessed. I have the portal set up on 'client indicators' layout, but I can change the client and indicator records on the 'client indicators' layout. I need the client/indicator relationship to be static. I have 91 assessment tables to use eventually, having begun my development with EC01 Indicators to begin with. Is it my rela…

Hi All, I would like to create a button that copies data from specific table and fields to other ones on display in my form. The context for this is that I am creating a button that copies data that has already been entered and displayed in some text merge fields. i.e., Account address (first table), and I would love to have that data copied to the location address fields (second table). See attached file. Any help would be gratefully received (Complete beginner). Cheers, Simon DB_SC_Grab- button question.tiff

I am working with a Woocommerce installation and trying to create a product form interface. Woocommerce stores product attributes as meta_key's and values. I was wondering if there was a way of linking the table to a different table which would be more manageable and easier to use. Below I have tried to illustrate the two different tables and was wondering if there was a way of linking them together allowing them both to update when information is entered. I have a feeling I might need to use global fields and calculations. However, not completely sure if it is possible. Woocommerce Table ID post_id meta_key…

Hi everybody, My client wanted to see the open balance from their "Invoice" table on their "Contacts" table so I built the following relationship ... Open_Balance = Sum ( pk_Contact_ID to fk_Contact_ID::Invoice Total ) - Sum ( pk_Contact_ID to fk_Contact_ID::Invoice_Paid ). Now my clients wants to see this open balance in a portal on another layout on the "Home" table, this is a "One" record table. Since this one record table has no Contact_ID I attempted to build the following "Relationship" g_Zero ( HomeTable ) < Open_Balance ( Contacts Table ) ... When I try to build this relationship Filemaker "Grays" out the "Allow Creation of Records ..." un…

Hello, for our annual conference, our database is people-oriented: Our main table, PATRONS, tracks the people who attend and all of the relationships are established around the events that people attend and the things that people do.We create relationships to various other tables, such as ACTIVITIES, SEMINARS, and DEMOGRAPHICS. As you would expect, one of the fields in PATRONS is Company, and when we sign on a sponsor, there might be several people attending from that organization. That presents us with a challenge: for these purposes,Company becomes the central organizing unit (instead of Last Name), and we would like to be able to view and manipulate our rec…

Hi, how to create automatically create same numbers of record in child table when parent table created. So both fields look same. Please help....

Hi, im searching for a solution which will let me save the relationship history of records. I want to have something similar like a "snapshot" of every record if it gets modified. in my example i want so save which employee had which jobs and childs over time, so i can view the past history of a specific employee. What do you think will be the best solution for this problem? Thanks in advice.

Ah, okay that makes sense! Thanks so much for all your advice. Okay, my next issue is as follows.... So I have a layout for 'Tracking Sessions' which contains details of each time someone went out to find a particular individual/group of cheetahs. Within this layout, I have a Tab that contains different portals to different tables of information that could be (but not always) collected during a specific tracking session (e.g. kills found, supplemental feeding, and 'Tracking Notes'). Now, because of the Join table how can I set these portals up to allow for record creation in these child tables while also creating the necessary records for the join tables? I've i…

At my cabinet shop we rely on a chart like the attached one to keep track of customer status. The possible statuses are: NOW - NEXT - SOON - MAYBE We currently generate this chart using CAD software. Is there a way in Filemaker to format a layout that would present the information in a similar format?

I need help, am designing a hotel booking system and i want to show available rooms when on the variable layout in a portal. i have tried lots of methods but all are not working. i have three tables, bookings,rooms and variable table which contain global fields. i was thinking of a custom functions to try and solve this problem. But don't know what to do. i need help baldly. thanks in advance

Hey Everyone! I am a little stumped. Let me first explain what I am working with to give you some insight: I have 2 tables that we will call Table1 and Table2. Table1 is the parent table and Table2 is the child. Now I have 2 fields that I need to display related data in each of these tables. In Table 2 I need to display and link data from Table1's field that we will call Names and in Table1 I need to display related field data from Table2's field that we will call Numbers. I need these to be able to be changed at any time so that Names field that is displaying on a Table2 Layout can be linked to a different record in Table1 and vice versa. So if w…

Greetings, Been away from FMP a while and having a brain fart. Two tables with the second table that needs fields from the first table updated ie(Color in table one needs to be automatically updated in table two) Table two (color)is a calculation and sometimes color in table one changes and i'm wondering if there is a way fmp updates table two without running a loop or some other manual proceedure. Thank you, Jared

Hello community, I have two entities, that have a many-to-many relationship. I have my Computers entity, and a Users entity. One user can have many computers, and one computer can be used by many users. I created my Computers and Users tables, plus a join table to have the relationship. The tables are OK, and work fine, I manually tested with setting IDs. My problem is that, on the layouts, I don't know how to technically and concretely relate two records from each table. I tried with external portal, but when I tried, it also creates records that I don't want. Simple: I would like to be on the Computers layout for example, and set a field so thi…

While creating a database that has Products, Suppliers and Lines I have come across the need to calculate a count of the suppliers from the context of a Products record. My relationship is like so: My question if I want to pull a lot of information about Supplier is should my portal in the Products record be from Suppliers or from Lines? If the best answer is Lines, could I still have a count of related Suppliers by using the primary key from that table in the portal? Is it a bad idea to use fields from a related tables portal other than its own? Thanks

Hi - OMG, I am at the end of my tether on this. Any help would be so much appreciated. It's probably an easy fix. Basically, I have a big database with a Company table and various linked tables (contacts, products etc.). It works well, and all the data is already populated, BUT I did a dumb thing on set up: the table relationship match field is the Company Name, which means if the company changes its name I cannot change the Company Name field as it is a match field (the portals on Company table layout just go blank as there is no match). I did a workaround (two name fields), but what I really need to do is have a unique ID (auto enter serial number) for the company table…

Hi, i am stuck for a few days creating an many-to-many relation and view/edit it via a portal, which shows the many-to-many. for a better understanding i attached a screenshot of the relationship graph. in the "Personen" table there will be basic informations of all persons (Name, LastName, Birthday, etc.) a person could be an Employeee (Mitarbeiter) with additional informations, stored in the "Mitarbeiter" table. (RoomNumber, OfficeNumber, etc.) a person can also be be a child. if an employee have a child (or more), the basic data for the child is saved in the "Personen" table and with the many-to-many relation the "Mitarbeiter.ID" will be mapped to the …

I'm working on a database to store/organise data pertaining to Radio/GPS collared cheetahs (e.g. movements, kills they've made, etc.). Within the database, my main table is 'Released Cheetahs' which contains all the information about an individual cheetah and their local ID number, which is called 'AJU#'. AJU# is the field relating 'Released Cheetahs' to the other main tables within the database (Fix Data, Kills, Tracking Sessions, Parturitions, Releases, Captures, etc.). Some of these cheetahs (and thus the records within 'Released Cheetahs') are in groups of a few individuals, which is delineated by 'Group Name'. My problem is that when I make a entry into one of t…

I run a school and keep track of everything school-related in an FMP12 DB on Mac OS 10.11. I used to be more immersed in FMP, but now I'm a bit rusty with some of the logic. Last year I kept track of all non-tuition expenses (books, supplies, elective courses) in one table (ItemPurchasers), and that was sufficient for manually tracking what each student owed. But it didn't allow me to easily see at a glance which students ordered Item X or signed up for Elective Y. ItemPurchasers was not a join table since I did not keep a table of Items to select from (hence the manual aspect). So in order to know how many of book X or elective Y....it seems that I need to mak…

I have two tables: - todo - response I have a list view layout: todo_list On the layout there is a single text area with the following merge tags: <todo::dueDate> <todo::detail> <todo::lastResponse> the field: lastResponse, is a calc field in the todo table If ( Count ( response::id ) > 0 ; GetValue ( response::user ; 1 ) & "| "& GetValue ( response::comment ; 1 ) "" ) The relationship to the response table, is sorted by ID descending. That way I get the most recent response to the top of the list for the calc above. While the performance is okay, I am wondering if there is s…

I've found a TO that is linked to the wrong primary table. I want to keep the TO—same name and fields—and link it to a TO of the the primary table ... not the primary table, itself. I can't determine a way to do that. I learned long ago that if I create a duplicate TO, it generates a "2" version ... and I have to change all the references in scripts and on layouts. Likewise, if I delete the original TO and generate a new one of the same name ... all the original references in scripts are lost. Is there another way? I tried adding a link to the correct TO, with the idea of then deleting the original link ... but it won't let me set two, even for a moment. Ev…

I am using FMP12 on OS 10.11.1. Each time I select any object in the relational graph everything within the relational graph disappears until I release the selection button. Objects do not disappear when selecting the relationship icon. I haven't found any other discussions about this problem on fmp12 on mac. Does anyone know of any solutions to this? I am not experiencing any other issues.

Hi guys I have a couple portals that are filtering via the portal rather than the relationship and and wanted to see if someone could recommend a way of changing this to filter purely by the relationship. For example: I have a company layout that has a portal to display each of the following: related invoices, related purchase orders and related projects. In this post I'll use Invoices as an example. The initial relationship does filter down to the related company record (company::id = invoices::id_company) however, I have multiple other filters that need to be applied, which is where I'm having the portal filter the results at the interface level. I have a…

I'm attaching a tiny 3-table Invoice example file I need help with (both a .zip and .fmp12). Maybe my (database) problems can be solved with a good, healthy relationship, and calcs? Considerations: • Product table could have 1000s of items • There could be thousands of Invoices, each with dozens of InvoiceLines, for a given Invoice Date range Needs: • For any given handful of Invoices, Found by Invoice Date range, I need to compute a list of which Products are ordered (Their IDs), and also the total Quantity of each. • I need these answers stored in fields, somewhere, so I can later reference these answers with scripts. • I need to know how to receive the ans…

Hi i created a database for a food manufacturing company, they need to be able to link the product with the company that ordered it with its ingredients and be able to link on e to another. i created it that from the product you can select a value list of the company that ordered it , i created a relationship. however every time i create a new product and assign it to a company a new company is created. i assigned automated id`s so i cant even link them together. please help me

I need a little bit of help. I am trying to relate an invoice table to my currency rates table, I want to always get the last rate rather than all rates. How do I do this? I have two tables Invoices and Exchange rates. An Invoice exchange rate would be €1.00 is 1.1006 31st October 2015 €1.00 was 1.1201 in 1st October (this might be the last time it was updated) How do I get the system to always default to the last value entered? So if I enter a new invoice today the rates will always take the rate entered today rather than the one entered on 1st October.

Im doing a solution for someone and they requested that when doing a purchase order, that one line item be costed over many jobs. ie. they want to purchase 100ft of edging. they have three jobs, each requiring 30 ft. I was thinking that when on the line item, they could click a button opening a popup that would contain a portal on it. The portal would allow them to select a job(s) to attach to the line item. This way when they could produce a purchase order for the whole order and also produce a list of material required for each job. Does this make sense?

Hi fellow logicians! I hit a brain wall here. I'm possibly just dumb, but I can't wrap my head around the ways to solve this problem. Consider the following scenario where we have three tables: Table 1 has records with a number field that contains arbitrary integer numbers, from zero to some unspecified number. Table 2 has (for example) two records in which we specify ranges: record#1 would contain (in two separate fields, "start" and "end") 10 and 20, and record#2 would have 15 and 90 in their respective numeric fields. Now Table 3 has many records related to both at once: it would contain a unique identifier from a record in Table 1 and an identifier from Table 2. …

I am using FileMaker 12 and I am trying to create a student-teacher database. Basically I want to create a roll sheet showing every student that the is enrolled to the teacher. The way I have it right now is I have a separate teacher table with a unique ID and their name etc. I have a student table with their unique ID and name etc. In that student table i have two fields (Teacher_1 and Teacher_2), since that student will have only 2 teachers. This field is a drop down list of the ID's on the teacher table. The way I have it now is I have it the relationship of the unique ID from the teacher table to be related to both teacher_1 and teacher_2 but when I go and create the…

Hi, I have a lot of returning bigger customers and i'd like to populate a field per client with their running total. Tried doing it with a script: Find client Copy total value Paste that value to their own global field Now i have to contanty refresch with the script. Is there a better way to do this? Through a calculation? The client field is called "Source" The total field is called "Year total" Thanks for any help/

Good day, I have a point of sale solution and i have also a stock module attached to the solution. Basically the main function will process each item sold from the Point of sale screen whatever the quantity and will reduce the stock quantity on the stock layout after complete the transaction. Also if ever i want to delete the complete sale i would like a script to re-update the stock quantity and delete the transaction. I am unable to perform this task and it was much harder than I thought. I have attached an example of my solution for your touch. I would also request if it could make it simple as possible. Thank you for your precious time and help. POS.fmp12

Hello, I'm working in a manufacturing company and i creating a filemaker solution. I' trying to make some relationship between some tables and it's now working for now. I will use a comparable subject to explain you my challange. Example: I have a restaurant, and i want to create Receipe. So have have multiples tables as Ingredients and the receipe. It's a many to many relationship so i have to create a list of ingredients. So in my table ingredients i have list of ingredients as egg, mayo chicken etc. i have to create multiple field like ingredient title, quantity etc. In my receipe table i have list of final receipe . ex: Omelet and checkien sandwitch etc. So …

Hello , im trying to send cancelled invoices to credit memo table with all the line items. What im trying to do is duplicate line items with negative values (negative values is to keep real inventory) but my loop is not working , it only duplicates the first row. Please let me know how do you deal with this ?

Hello i have a csv file with Cap, city and state. How can i Autofill city and state in filemaker when i create new record and i insert Cap? It's possible? Thanks

Hi all, I've got a problem I've been struggling with for a few days now and I'm hoping someone here can help. Apologies if this is actually quite simple. I'm not a complete beginner with FM, but far from advanced. Anyway, here's the problem: I have a parent table called Collections, and a child called Items. 1 Collection can have many Items. I also have a table called Requests. This table should allow a user to request a selection of items from a particular collection, so that these items can be physically delivered from storage to our premises. Each Request should be unique and retained as a permanent record, allowing us to have a history of when Requests were made and…

Someone know how to disable the automatic sorting of records? I have several layouts that are sorted and I want to maintain the original sort until I finish to update the information of the records showing there, but any change that I made, produce the system re-sort the records. It is getting very annoyed. Is a way to stop that? Thanks in advance for your help!

Hello, I started using Filemaker some days ago and I can't find a solution for what I what to do. I have to tables. One with patients and one for visits. As you can see from the images the patient table has some button (visit 1-2-3-4). What I would like to do is this: I create a record with patient 001, then i click on visit 1 and this create a record on table visit that must be related to patient 1. The same thing for patient 2. When I click on visit 1 on patient 2 I want a record on visit table related to patient 2. Also the button has to show the record so when I click on visit 1 I can add all the information. Can someone please help me out. I'm studying on a course bu…

Hello again, long time since I visited. I have setup a curling membership database to record all pertenant info; being which league, the team they are on, team position etc. But now I want to create a new years worth of data. I thought about this at its inception but time got the better of me and I negelected it. My thoughts were that I create a new year table, then create a new record in that table. then, when I create new team or member have it populate a current year field in the respective tables with the value of the current year. or curryr_ID. This way when a new record is created, it will have that ID attached to it. Follow me?

Hi all, I've been getting more an more frustrated with the way Filemaker works. I come from and MS Access background and I'm really struggling with Filemaker. I want to learn this, so please forgive me if I dont just hand over my database for you to look at. I did this and someone dazzled me with there brilliance and I ended up deleting the database because it was just way over my head and I could not replicate anything. I am only very new to this program, but I do understand the basics of relational databases I have a project concept that I;m working on of which I have done many similar projects using MS Access. Part of this is what I'm working on now, but I'm just not…

I'm using the Selector-Connector principle to build my new DB, and use the "MagicKey" approach to create records where needed. One of the advantages of both methods is that I pretty much have no need to jump from layout to layout any more to create records. I have stumbled upon one issue though. To create a new record in a table, I have two table TO's that have the same base table. I use a global field as a magickey. Whenever I want to create a new record in a table, I clear the global MagicKey field in the parent table, and then set a value in one of the fields in the child table. This works beautifully. Except when the table is empty (ie no records). See attached DB a…

Hi all, I'd like some advice on a solution I'm working on. The database i'm creating has the following elements: Artefacts: these can be e.g. podcasts, e-tutorials, books etc Artefacts are grouped by module, i'e a module has a number of artefacts: Projects and tasks: this part (Left of graph) is straight from the Filemaker solution. I originally started with Artefacts purely for tracking production and have subsequently joined it to the Projects and Tasks. The purpose of the database: I wish to allocate users tasks and join the artefacts with the tasks. This can be done individually or by module. I used the table Task Artefact Join to accomplish this One regular …

Can someone tell me what is wrong with this way to find duplicates. It worked with me for 1 table only and not working for others. Define a self-join relationship. A self-join is a relationship is a relationship where both match fields are defined in the same table. Use your chosen identifying field as the match field in both tables in the relationship. Drag that field on top of it's self. Name the relationship occurrence "Same". When you define a self-joining relationship in the relationships graph, FileMaker Pro generates a second occurrence of the table upon which you are basing the self-join. FileMaker Pro does this to prevent the relationships from forming a cy…

I have a relationship of: Clients ---< Policies ---< Renewals On the Renewals table, I have a field named "Employees" which tracks the number of employees a Client has. Renewals happen once a year for each policy. One client can have multiple policies, and as a result, multiple renewals a year. I need to be able to track the total number of employees each year, but since Clients with multiple Policies will have employees registered under both policies, it results in duplicates in the Employees field. For Example: Client A - Policy 1 - 2015 Renewal has 50 Employees Client A - Policy 2 - 2015 Renewal has 30 Employees Client B - Policy 1 - 2015 Renewa…

Hi, I need urgent help - This attached diagram explains it: I need to know how to create the relationships and how to link it on the Main Order Table. ALL help is appreciated.

I'm very new to file maker so this may seem easy to some. I have built a database of a personal info and company info for that person. I want to be able to create just new company info for that record using same personal info then cycling through the different company info for that person. can anyone help or point me in the correct direction. there is no info in the file so can happily share if u need further details.

Hello, Can anyone help me please? I am very new to FileMaker. In the first image is a sketch of what I am trying to achieve. In the second image is how I have tried to solve the many to many relationships in FileMaker. As there are a couple of repeated table occurrences I feel a bit nervous that I have done it incorrectly. Any help would be greatly appreciated, thank you.

Hey guys, I could use some help. I'm setting up a database and I'm a little stuck. I need to create a database with 90 records (machines), and each of those 90 records need 288 subrecords (positions on machines), and each of those subrecords need another subrecord to display info about said position, and I have no idea how to set this up. Currently I have 3 tables MACHINE POSITION POSITION_CONTROL I created 288 in the positions tables because theres always 288 positions. The user needs to select a machine, then a position on the said machine and then access the control sheet of the said machine on the said position.. Is there any other way to make this work oth…

Hi folks, I have a database for my school music department. It includes these tables: Pupils (things like DoB, name etc - also baseline test results and previous school) Marks (linked to Assessment and Class tables too) Instruments Instrumental Exam Results What I'd like to do now is: Add GCSE, A level and A2 results Use previous baseline tests, Marks and Instrumental Exam Results to generate predictions so I can see which pupils are likely to under and over achieve. My two questions are: How do I go about thinking of the maths (I'm a musician!!!) The GCSE, AS and A2 exams all have 3 papers, each with different maximum marks. Obviously all could be convert…