Relationships

I am working with the tutorials and I have created 4 tables which i have linked with primary and foreign keys. We have a business that works with companies that manage multiple buildings. so each one client will have 100's of buildings I need to link. I have done this with a one-many relationship using a primary key for the company table and a foreign key in the building site table. I am creating a layout that displays both tables which i can get working to a certain degree...the main problem i have is I can only retrieve one record...i have gone into find mode typed in some details of other records i have entered but it still only retrieve the same record over a…

I recently read a post or a blog or something describing a technique for creating a related record using a global field to trigger record creation through a relationship. I have been trying to find it for a couple hours wiith no luck. Can anyone point me to a post or blog that describes this technique? Thanks Ron

hello all. This is my first experience with filemaker and my first post. I am using filemaker 11 to create an asset database. I created it from scratch and one of the big things I wanted to have was the ability to relate some CSV-based tables that get downloaded from some asset tracking solutions that we use. I have a table "hardware assets" that is related to a table called "cva_assets". CVA assets contains information from our leasing company. My plan is to download the leased asset list from them and place it in a location where filemaker would be able to do a recurring import. That way, I would easily be able to populate lease information for the ass…

Working on a simple form builder. This will act as a preference to what type of items belong on a form depending on the event type and form. section (events) section_type (fund raiser, members dinner) form (proposal, claim) form_Item (field1, field2, etc. ) The question is, since the will only be a few records (100 maybe) would you just make this a single table with a bunch of self relationships, or four tables? SINGLE TABLE id id_parent name type

I appreciate that this is probably a basic question but I just can't figure it out. This is an Electronic medical record solution. I have a table for "doctors" in which each practitioner has a unique id. It is related to a "Location" table that contains the addresses at which each practitioner works and it has a location id. As it is a one to many relationship I have created a join table which contains of course the practitioner id and the location id. The problem is that each practitioner has a unique provider number for each location. Thus each provider number is dependant on a practitioner and a location. I have a layout in the practitioner table with a portal …

Howdy, I have held out posting here, hoping to come up with the answer myself, but I am finding that I am going around in circles! Could someone please nudge me in the correct direction? Although this is not an "Invoicing" solution, the analogy works and is one that many are familiar with. The Scenario: - User creates pre-populated invoices in advance, using any number of available products, and places them on the invoice is a particular sequence. - Each product may appear on any invoice, but only once. - Each invoice is unique and once created, is not able to be modified; if changes are required, a new invoice is created with the modifications.…

Hello Board, Please excuse my general ignorance. I have used FM a long long time ago. We are a small manufacturing company and a week ago our current software basically broke. It was being installed over a 6 month period but essentially it was too complicated for us. So, the software has been ripped out and we have reverted back to a paper based system. Details are being entered into our Sage Accounts software so Sales Orders can be allocated with stock and despatched. I have been tasked with building a very simple database that we can begin to expand. So, i wanted to start by using the Starter Solutions in Pro v11 to build components of the syste…

Hi all sorry for any noob questions, I'm fairly new to FM PRO,Ive been reading watching videos but I'm stuck right now, what I'm trying to do is a loan DB i have only 4 layers right now (Customers - Loans - Loan List - Payment). I'm able to create the customers and create the loans I can even view my loan list, my problem is in the payment, what i want to do is from the loan list click a customer and load that loan in the payment layer so i can make payments to that customer i will upload my file for you to see, sorry if you see errors I'm really new to this, any help would me appreciated. Thank you Chino DB.zip

Hello, Hopefully I have chosen the right forum for this question. As a part of a database that I am developing which deals with portfolios of mutual funds I need to record a buy transaction of a mutual and a sell transaction of a mutual fund. I have created a table called new money into investment which records any new [outside] money invested into a mutual fund in a particular portfolio. I also have a table which records sell transactions where mutual fund units are sold and the resulting cash is taken out of the portfolio [say, into my bank account] that holds the mutual fund units sold. Now, here I am unsure of what to do: There is the situat…

Hello, first time poster and FM newbie. I'm creating a DB that tracks sales for a small retail sales team. One table called "Quarter" contains a primary key called "__kp_Quarter" which is a unique identifier of a fiscal quarter. An example would be "2012 Q1". There are also two fields, startDate and endDate, which the user sets to the start and end dates of the quarter. This is entrusted to the user because lengths of fiscal quarters may vary. In another table called "Sale," which tracks individual sales, there is a "Date" field. This is also edited by the user, because some sales are backdated. I'd like to have the field _kf_Quarter, where the content is …

If you click into a dropdown field on a layout and hold down the command (apple) key you can select multiple entries from the dropdown. We use this featue on occasion but the layout doesn't show the values after the first one becuase a carrige return is automatically inserted after each entry and the layout only shows one line of text. Is there a way to make the additional items selected be visable without changing the layout to show more than one line of text? I was thinking of making a new field that copies the text from the first one and inputs it into the new field with a comma and space in place of the carrige return. So if a user selected multiple items, t…

Sorry if this is the wrong place to post this; but I thought it related to database layout. My organisation needs to collate our client's contact information, scans of archived correspondence with those clients and information and articles about issues raised in the correspondence. We believe it would be easier to create two separate databases to effectively manage these different sets of data. One uses the standard contact management starter solution layout, which has one record per person (for contact details), the other uses the standard document library starter solution layout, with one record per document (for archiving correspondence). This seems to be the best solu…

Hi, I have few FM DBs which has same table and structure inside. is it possible to create Master db where I will merge all external tables together to one master table? Solution shouldn't one time solution but dynamic.

Greetings all, I am attempting to generate a report that has fields from two tables, Companies & Stocks. The tables are related based on CompanyName. In this database a company can have multiple stocks. When I create the report, utilizing a few fields from both tables, only the first stock that has been entered into the stocks table for each company is shown. So, if Company ABC has stock ABC1, ABC2, and ABC3, only the one which was entered first will show in the report. What I need, however, is for it to show all stocks under any company. One thing that I did notice is that if I changed the name of a stock that is one of many of a company, it then showed up in t…

i am working on a royalty distribution system and the relationships are becoming confusing. my scenario is this. i have customers (table) who order one or more products (table). an order is put into an orders table which points to a line items table with the usual data fields (customer ID, product ID, price, etc.) the money is split between partners. not all partners share all products. so i created another table of contacts (not customers, but partners). in the products table, i have a portal that shows which partner is related to that product along with a percentage. example: customer john buys a copy of a cd and pays $10.00 which appears in the line …

Greetings all, I am creating a database that will enable me to track a company's stocks, options, and bonds. Thus, I have four tables, which are Company, Stocks, Options, and Bonds. Each has its primary key (company name, stock symbol, bond cusip, option symbol). In each table is a respect foreign key targeted towards the other tables, so as to related them. My goal is to be able to look at a company's profile in the database and via a portal or even related fields from related tables have their securities shown. However, I am at a complete lost for creating the relationships. I've attempted it a few times to no avail. I am not sure if I need a join table or whatever…

I am having trouble counting related records between 2 databases. I have an Acquisitions Database and a Products Database. Each acquisition produces multiple products. Each Acquisition can be acquired multiple times but at a different price with a different # of products coming from the acquisition. Here's what I've done: I've related the product quantity table (related to the product table by a sku)in the Product Database with the Acquisition table in the acquisitions DB through an Acquisition ID and a date Acquired. The product quantity record records if the item is in stock and the cost to acquire for that item. In order to determine the cost to …

I have four old fmp 6 databases that I have converted to 8.5. Now I'm trying to establish relationships between them, but I cannot figure out how to add each database/table to the Tables window for each database. I assume I need to have each database/table listed in the Tables window in order for them to be used in the Relationships window. The current name of each database are; Customers, Invoices, Products and Salesmen. Each database already has primary/key fields, and lots of data. Thanks!

Hi, I've got a database I am working on and I need some help with a relationship on it. I want a picture file in one Layout to appear in another. I've done relationships before but I am struggling to make this work. The record name is that it needs to correspond with is from a calculation. I've done a mock-up of what I need in this attached database. If anyone can help I would really appreciate it. Thanks, Ro PictureTest.zip

I have 2 related tables.one of them is B,the other is C, There is a Button in B table, When I click the button, I want the texts in fields to copy to related table C.

Happy New Year to Everyone! I wonder if you could be so kind as to set me in the right direction. I am having issues getting the database structure right in a very simple Bank Cashbook displaying the Monthly Deposits and Withdrawals. My Cashbook Layout lists the Month and Year in it's two fields with a Portal for Deposits and Portal for Withdrawals which should display all the Deposits and Withdrawals for that month only.(Screenshot attached) I only want to work in the Cashbook Layout so my problem arises that because the tables are related by a calculation, I can't add a new Portal Row to create a new record in the Deposits/Withdrawals Tables without a Script. …

I have a project where there is a main contact DB (hidden), and then a separate DB for each role that contact could be. The separate role DB's are because each role is handled by different departments, which will have their own layouts etc. that need to be separate and tidy from the other departments. There will be issues when creating new records, because the user will have to first check to see if that contact already exists, but I can deal with that. What I am curious about is which structure to go with: 1. In the contact table have a foreign key for each role 2. Have a join table called 'role' to link all the separate DB's together. Note some roles…

I have a Data base that can three variables for addresses. On the Company layout I can see all three and all is fine. But on my Address Layout I cannot see the Company name as a related field for quick reference. Why not? I can get it to work as a look up. I have included the file for reference. Any help appreciated. Thanks in advance

I've seen advice that strongly recommends people stick with the serial ID system provided by Filemaker. However, the reason I am looking for an alternative ID system is the difficulty in trying to recall a number for a certain artist or a concert. I've been thinking of "creating" a unique ID for one each Artist in one table; another unique ID for each Concert in another table (and possibly a unique ID for each piece of Repertoire in another table). For instance I'm playing around with this idea: S1 2011-12-25 This would mean the "Season 1 December 25th 2011 concert". It is easier for me to generate, recall, input this ID than "34" for a certain co…

I've been struggling trying to figure this one out and am hoping someone can help me out as I think I'm now going in circles and confusing myself! I have an Order Tracking database which includes a tables for Customer Contacts, Orders and Customer Order Status. The Customer Contacts are individual records for each person in a company and there are multiple companies. Therefore, I can have multiple records for the same company. All the records have a common company name to tie them together. The Orders include the Customer Contact and all the order specifics. There are multiple orders for each company and can be multiple orders for a person within a company.…

Dear All, I am just setting up a database for a concert presenter of classical, jazz, world music, dance shows. We present about 70-80 concerts each season We are in our 6th season, and I am trying to organize all the data from the beginning to present (about 500 concerts). I am trying to set up the following tables: 1) Concerts (purpose: to create a snapshot of each concert's data) 2) Performers (main purpose: listing each performer's concert appearances and the repertoire he/she has played; secondary purpose: personal contact information) 3) Repertoire (main purpose: to display which repertoire has been performed when, and by whom, so that r…

Hi, I have a problem which I know will be easily solved but just cannot work out what Im doing wrong. I have two tables. Clients and Episodes. These tables are related via a field called "ClientID". The Episodes table contains date fields called "Opened" and "Closed" If a record has a date in the "Opened" field but not the "Closed" field, then the status of that episode is considered opened. From the Clients table, I want to be able to determine how many records in the Episodes table have that status of open for that client. As I say, it is a simple thing but I'm doing something fundamentally wrong. Do I need to setup a different relations…

Hi, I'm having a headache. The problem I have is that I'm making a database for my company. We wish to have two separate databases; offers and invoices. Both of these contains a related table that is designed the same way for both of them (the invoice database is intact a copy of the offers skeleton); a custom product (which is defined in a new table). I want to easily copy values from my offers database, into the invoices. Problem is, since the custom products are related, I can't find an easy way to copy values into the correct fields. Example: A customer ask for an offer for custom products A, B and C. We create a new record in the offers table, create a ne…

I am designing all the standard relationships according to what I have learned. I now need to add another piece and I am not sure how. There are two parts: 1) when we are talking to a customer and if the customer displays 'interest' in one of our products, we want to quickly capture that and I think it should be a related table to Customers or Products. It would just log the timestamp, CustomerID, ProductID. Where would I relate such a table and how could I view a list of over 15,000 products quickly? actually, I think portal with global trigger to filter so I can probably figure this display part out. It is the relationship I question. 2) I have existing…

Hello, I wonder if anyone can help me with a problem with relationships. I have 2 tables, clients and contacts, due to clients being able to have multiple contacts, I have a portal on the clients layout where you can enter the contacts. I am now trying to create a list view that shows both the client name and the names of the contacts working there. ideally the client name would show up once with the contact names listed next to it. My solution was to have a client name field on the contacts table that copies the Client name into it when a contact is added. The tables are related via a contact ID and the list layout is based on contacts table via this relationsh…

Hello, I wonder if anyone can help me with a problem with relationships. I have 2 tables, clients and contacts, due to clients being able to have multiple contacts, I have a portal on the clients layout where you can enter the contacts. I am now trying to create a list view that shows both the client name and the names of the contacts working there. ideally the client name would show up once with the contact names listed next to it. My solution was to have a client name field on the contacts table that copies the Client name into it when a contact is added. The tables are related via a contact ID and the list layout is based on contacts table via this relationsh…

Hello, I am setting up a single database to manage our university's music department current setup of multiple databases. This is a ground up redesign and I'm needing some confirmation that I'm not doing anything the long way around. FYI, I'm using the FM Starting Point database as a jumping off point (although I am copying it manually so that I learn more about filemaker) for basic setup, design and layouts. The main conceptual trouble I'm having is with my Students table. We have hundreds of potential students every year that we track for recruiting purposes (and for their participation in a summer camp, honor band, etc.). I need a way to keep track of st…

We have a FileMaker Server Advanced that hosts just under tables. Within those tables, there are hundreds (if not thousands) of database relationships. We just recently moved to a new location. Part of that move was implementing a new network schema and active directory. Is there a way to perform a batch update of all database relationships?

I may be having a bit of a senior moment here but wanted to put a question out to the room....... I need to create a price list. SImple enough eh?! This list should have 2 'sets' of management. Firstly should be a list of aircraft, priced per hour second is a list of types of lessons in those aircraft each aircraft has a different price for a different lesson etc the management should be able to add aircraft and lesson types as they see fit. how would you approach this?

Hi, I have a database with let's say 3 tables: People, Object, and Measurement. I want to use calculation to create the unique ID for the Object and Measurement table. For instance, the object_pk would be "PeopleID_ObjectDate" and the Measurement_pk would be "PeopleID_ObjectDate_M_MeasurementNumber". Since these ID are calculation fields, I would also have to make the foreign key in the other table to be calculation field so that the ID numbers match. However, I would have to specify what the calculation is if I specify the field as a calculation field. Should I use a lookup value then? The reason I am creating ID in this fashion is that sometimes the people, ob…

I'm trying to build a DB that I can enter in a customer, and how much he spent per month, then track who referred him. The problem is Customer A earns 1 point for every dollar spent in a month, he also earns 1 point for every dollar spent by someone he refers (Customer . But Customer B refers Customer C. Customer A receives 1/2 a point for every dolar customer C spends, and 1/4 point for every dollar customer D spends, who is referred by Customer C. I sincerely apologize for the terrible and confusing example, but I need a way to enter in every customer's amount spent, then caclulate the number of points each person earns in that period... Any help would be …

Been staring at this for far too long, I know I've missed something so simple I'm going to kick myself! Just want to able to click on a portal row and have some details show up, if you open the attached file you can't miss it. Automotive.zip

I have a main table (Patients) with many records that I want to represent and quantify data from in an analysis layout using interactive charts (method here: http://filemakertoday.com/filemaker-resources/filemaker-techniques-examples/item/216-creating-dynamic-summary-charts-filemaker-11) What is the best way to do this? I was planning on creating a new table (Study_Analysis) and using a Cartesian join between the two primary keys. Is this appropriate? This seems like a simple question, but my only other idea was to add my new fields to Patients and display those records on the analysis layout, which might mean running calculations on all Patient records when I ente…

Hello! I can't get a lookup matter. The situation: I've got 2 tables -- "List of Contracts" and "Trades". Those tables are related by the field ID Contract which is in both tables. Any Contract relates to several Trades. The table List of Contracts has a calculated field "Multiplier" that I want to use as a lookup for the field Multiplier2 in the Trades table. I think that I established lookup options properly (not too many options there) but still can't get any value in Multiplier2 field. Would be grateful for any suggestions. Thanks in advance, Serge Contract.zip

Immediate real problem: I did this entire project without considering how to script it at the end and it is 99% done and now I hit a snag. That is like building a bridge before knowing what river it might cross. I will not make this mistake again. It is writing to variables for virtual columnar reporting using filtered portals with GetLayoutObjectAttribute(). Relationship (actually all TOs are appended with ~reporting but I dropped that for simplicity here): Accounts::AccountID =Transactions::AccountID and Accounts::gStart <= Transactions::Date // corrected the operator. I had it reversed originally. and Accounts::gEnd >= Transactions::Date //…

Instead of trying to explain my problem it will be easier to show (Attached file Automotive.zip). Head to the Vehicles Layout, click New Service Button. Why won't the Service Layout update the fields unless I click on Service Date, pick a date and then click out. Been going over this for hours and it still eludes me. Many Thanks Automotive.zip

Greets, all: Please refer to the attached files. I'm trying to set up a report based on the data from two different tables, Job and Resume; the report is in the layout, Printout (also seen in the attached screenshot.) I just can't get the relationship right for it to work. :S The current solution doesn't have the join table, Job-Resume--I just added that in to see if that's the path I should take or not to obtain a one-to-many relationship between the tables...if that's the ticket. The best I could manage so far has been either using Resume as the "parent" table but all I get is one company name, not all, that used a specific ResumeName. Or, I'd get results if I base…

How can I filter a portal by a calculation field that references a related child table? I want to filter portal by "Working" and "Complete" but relation fails since the sorting field can not be indexed. For some insight, tables set up is ParentProject > ChildAssignment I have a ProjectTable self relationship built, and want to sort this portal by "Working" and "Complete". I have a global field that user can choose filter portal by "working" or "complete" The global field is related to a calculation field that looks at the AssignmentChildren. So working with three key fields here... 1) ParentProjectTable > GlobalProjectFilterKeyField; User us…

I often want to ensure that, if a user deletes a record in a parent table, all records in a related child table also get deleted (cascade delete). I can ensure this in the Edit Relationship dialog by checking the option "Delete related records in this table when a record is deleted in the other table". What if I want to prevent the user from being able to delete a record in a parent table if that table has related records in a child table? What are my options? Is there a way to ensure this in the database schema or do I just have to rely on the scripted control of my user interface?

Please help a moderate user. I can do basic joins with little problem- but having trouble with this twist. FM Adv 11 on Mac. Need to join two tables (say A and B ). Table A varaibles has a ID, Beginning Date, Ending Date (like 444, 07012011, 07302011) Table B also has an ID, and a service date (like 444, 07152011). I need to join records IF ID = ID (not a problem) AND IF service date in B falls between beginning date and ending date in A. It is this second one that gets me. I have only done = joins, never a "falls between" two variables join. Any help?

Hey Gang; First of all, i have a complex business that offers tons of custom options so this is my problem which i cant seem to figure out. Design wise i think is my issue> Eg. User does a basic sale, i only need a simple portal with [name > Description > Price >Qty > Ext price ] However if i am adding a Lady suite in [Name ] --> i want the user to choose other options like [ cake (flavor, filling) | cupcakes [flavor(s) ] | stand color [white/gold/silver] | due date | message | due time| ] Questions; would i create a separate portal for the suite ? if i do that, how would i retain this information as well as the other porta…

I just learned about using link tables for one-to-many and many-to-many relationships. When should I do that as opposed to just creating set fields (i.e. visible on all records, even if empty) in the database? When the many list is HUGE it makes sense to make a link table. When there are fewer options, set fields make sense. It seems the goal is to avoid storing data in records that doesn't need to exist. But here's an example. Every patient (record) in the main table has (at least) four coronary arteries. We are recording the findings on autopsy for each vessel. But the autopsy person doesn't always record each vessel. So sometimes we get 0, sometimes we get 4, and …

Hello Everyone; just trying to figure the best design or way to go about designing this. I am wanting to keep track of my customers orders, items the had ordered in the past and put all the information in a history table for Perhaps a Customer Relations Management Table for later use. Also, for reporting. What would be the best way to design this from the start. Or would this just be a Simple relations TO with CustID -- > OrderID ( by date, etc,etc) any help of designing this from the onset will be great. thanks again, i

Well I am now getting cross-eyed trying to figure out what I missing... Simply I have a table [table2] with four fields [code_ID, code_Name, Code_description, Code_pricing] where I want to get info for another table [table1]. I can get informationf from table2 by relating the code_ID from the first table. The relationship does bring up the correct relating information. However when I add new fields by duplicating the exisiting fields and changing the names [i.e. code_Name2, code_Name3, etc.] to table1, I can only populate the relating fields to one collection of relating information tghroughout all relating fields. My relation is code_ID = code_ID which seems to…

pls help me in a function: i have some record on a particular day field and time field, such as- on 8/11/2011 2:00am. Suppose on this date and time, i have 4 appointments . Now how would i count the no of record on this date & time with the help of function.. every appointment has a id. Please reply soon.

Hi, I have a table set up right now that consists of records that are individual jobs we do for customers. I partial bill several of these jobs every other month and am wondering if it's possible to create a related table specifically for partial bills for these individual jobs/records. I'm hoping this will then enable me to create as many partial bills for each job/record as I like. Somehow I'd like to set it up such that from Record/Job# 1, I can simply click a button that says "Partial Bill" and it will bring up a related table where "Job #1" is automatically pulled in and then I can enter the data needed to do the billing. I want the Job# field to be the field t…

I am restructuring the data in an existing database and have come across a problem. The database I inherited had several layouts that were all supposed to have the same number of records, each record representing a single patient's medical data and each table housing data on a specific medical area of interest. All of the tables are linked by a unique patient identifier. However, not all of the tables have the same number of records in them. I need a way to find out which patients do not have records in each table and then create records for them to equalize the numbers. My ultimate goal is to export data from the fields that currently exist (primarily one HUMONGOUS…

Hi guys, I have 3 tables I am working with in my Database:. tbl_Location, tbl_Warehouse and tbl_Issue, related to each other. tbl_Location to tbl_Warehouse (one to many) and tbl_Location to tbl_Issue (one to many) Depending on what locaiton the user enters a dropdown value list displays of all the Warehouses for that location (anywhere from 50-75). Also depending on the location that is chosen a drop down value list appears of common issues to that location. I need to add a field "Section". The way this would work is depending on what Warehouse AND Issue the user chooses (combination of both) a set value list would appear for section. There coul…

This is a general question but not hypothetical. I have global dates (start and end) in a regular data table (Accounts) because they are used in relationships. When need arises to insert one of these globals into a calculation, does it matter which table occurrence of Accounts is chosen? It seems no because FM sees them from anywhere but knowing for sure would be great. With regular fields it seems important to pay attention to the lines back through things and use one that is not only related but the best choice; properly filtered, direct route. So, if in a related table anyway, does it help to choose that related Accounts to get the global field or does …

Hi guys, I need to create a layout that when one field is populated with a value, the other values populate automatically based off of the value. The process is the user will front load data via Excel, with about 10 columns of data. One of the fields is "SIID". What I want is a layout that has all the field boxes blank, and when the user enters a specific SIID the rest of the fields populate with the related data. I am guessing a portal is the way to go here, but currently I only have one table. How should I work this? Thanks

In general do you all like to move around in layouts during a script or do you like to stay in one place? For instance, do you create a relationship with allow creations on and set a field through the relationship or do you all like to grab variable and go to the other layout and create the record and then back? It does not seem so important when you are talking about one record but when you must loop through a bunch of records and, if there is no related record, create one, then what? So I don't mean during data entry. No user involved in this. Let us say that I have script which needs to regularly create a record and since I am busy with my script, I woul…

I am trying to resolve the following issue. I am building a database for a non profit museum. One donor can have many gifts. But it is also possible for a donor to loan an item rather than gift it. Do I simply need a field under the gift table that states whether it is gifted or loaned, or should I have a separate table for each type, i.e. gift or loan. This is made more complicated because a loaned gift can later turn into a donation. Any help is greatly appreciated. w buch

I once attended a lecture on Database Design by a prominent bio-infomatics designer who started the lecture by saying "Any idiot can design a database... and MANY do." In an effort to avoid being an Idiot I'm trying to review the Filemaker Standards and revisit design in general. It's clear to me that my last design was half-assed (as I look at it now). It is functional but not well designed and it leds me to a general question about the very basics of relational databases. What is the criteria for establishing a table? Example: In my last database I created a table for "Clients", another "Relatives", another "Doctors" etc. I realize now that I should …

I'm not sure exactly where this SHOULD be posts but this seems as close as can find. Here is my situation. I created a database with, among many others, I have a table for Clients and a related table for the medications they take (all related nicely). The medications table includes Brand name, generic name, strength, dose etc. I also created a report following a predesignated format (need to stick with this format) and one box in the form requires a list of the medications only (generic or brand if there is no generic). I don't need any other data from the table for this report. So essentially I need a list formatted as so: drug1, drug2, drug3, drug4, et…

What is data integrity conflicts? And why it is best practice to prohibit changes to primary key fields? In my case, the primary key in my product master is alphanumeric and I need to delete or change the items sometimes because there are new items with same item number. I wonder if I should have product id and product number in the product master table so I can have two same item numbers, one has new packaging and another has old packaging. Many thanks. K. C.

Hi Guys, I do have the following structure: Finished product (related by id) RecipeInsert (related by recipeID) RecipeDetails Finished product (related by id) RecipeContainer (related by recipeID) RecipeDetails Couple of products has the same recipes, ie: Icecream vanilla (packed into box with label) Icecream lemon (packed into box with label) I have the problem using data from Finished product table in RecipeDetails table Ie. I have in Finished product table the info about the product weight I want to use in RecipeDetails table for calculation (ie. to get the information about necessary quanity of the boxes). Because the recipe is …

Hello my friends. I'm new in this FileMaker world and i have a question with wich i think you could help me. I have a contacts database and i would like to create a command so that one field could be automatically filled accordingly to the information in a first one. Let me try to make it simple to understand... Imagine i want to write a letter to a car factory. In this layout i have a "name" and an "adress" field. What i want is to look for "Automotive Parts, Inc" in the "name" field and the "adress" field to get automatically the remaining information, let's say "Ferrari Street, F40 Modena Italy". Is this possible? How can i do it? Thanks in adva…

When I go to delete a quote within its native quote table I get the error code 407 "One or both match fields are missing (invalid relationship)". How can this be when I'm deleting the record within the native table and not through a relationship? Any help or direction to where this may have been discussed before would be greatly appreciated.

New to filemaker, I'm discovering relational databases but getting stuck I'm creating a small database for the necessities of a golf match play championship. I've created 3 tables: -players (name, first name, tel, email, adress, etc) - individual rounds (id round, date, name, first name, gross score, net, ...) (NB 1 round per player) -matches (id match, result match, score match,id round 1, name player 1,id round 2 name player 2, score 1,score2, etc) I'd like to link the 3 The link between players and rounds is fine: the info from my players records appears correctly when I fill in the last name. 1st request: is there an "auto fill" feature whereby while…

Hi I'm quite new to Filemaker and databases in general and I'm struggling with solving a concept which I am assuming is natural territory for Filemaker and databases in general. I just can't seem to figure out what to to do in terms of relationships. I'm convinced the solution should be elegant and simple but I am really stumped. In addition to other features I want to create a database that will track an inventory of raw photographic chemical consumables some of which will be combined to create other consumables which again may be used to create others before they are actually used in a process. Basically, I need to track things like chemical solutions that I…

I have a database consisting of records relating to activity: timeslips. The records are used to prepare time based invoices. I Have a separate database with a single index field which I have related to timeslips using the index field. I have a portal using that relationship which is filtered so that I can see the values of records enteRed in the last 7 days. This is then summed. I am sure there is a less cumbersome way to know from moment to moment how much work I've done in the last 7 days....

I've got a field on Layout A (from Table A) that displays a values from a value list in a dropdown. The value list displays values from Table B which is a personnel table. So the field on Layout A displays "Last & First" when the dropdown is clicked in browse mode. I would like to set up a button action to go to the related record in the personnel layout but something isn't set up right, I'm guessing in the relation between the tables. How should the relationship be set up to get the button action to work to go to the related record in a different layout/table? The field the button action is on is "Safety Clearance:Primary Authorized Employee". The value l…

Hello all, a FMPA11v4 Mac beginner user here trying to design a human resources database to track employees and their promotions throughout their employment in a large publicly traded company. Attached is the ERD which I hope has the right idea. An employee can receive many job ratings, and a rating can be shared by many employees. An employee can have many job positions, and a job position can have many employees. A department can have many locations, and a location can have many departments. A department can have many positions, but each position can only have one department. A division can have many departments, but each department can have only one division…

Hi Guys, I am newbie to the problem I have faced. I have the following structure of the database: Production Plan (plan ID to plan ID) Plan Details (product ID to product ID) Products table (Recipe ID to Recipe ID) Recipes table (Recipe ID to Recipe ID) Recipe Details table (Material ID to Material ID) Materials table In Production Plan table I keep info about what I produce when, in what quantity In Recipe I keep reference to Recipe Details In Material Database I keep the info what is the price of each material. What I would like to do is: 1. Using the recipe details calculate the sum of materials used (let say I have to produc…

Hi all, I'm very new to this and trying to build a database for film and video editorial management. At the moment I have built a database (based of a starter solution for the moment) to track every shot and where the assets for that shot have come from. What I want to do is have a separate table that has the project information in it that I can enter in once and it will appear on multiple tables or databases. I am having trouble with the relationships between the two. I am attaching the file for reference in case it is easier to take a look. Cheers, Ro EditorialManagementv1.zip

If the filemaker training series was available as a download, i would have ordered it NOW!!. DOesnt mmake sense to not have it as a download. Any other comments on this? -i

I have a table of personnel which includes a "department" field. There's a related table, with a layout that has several dropdowns that all have the last and first names of personnel as the data to display. Each field on the layout corresponds to only one or two applicable departments due to the nature of the form and it's purpose. For that particular field on the layout, only names from a certain department would ever be selected. Having ALL personnel show up just clutters things and makes the dropdown list huge. I was working on a conditional value list but I'm not getting anywhere so I'd like to take a step back and get everyone's input on what is the best …

I have a courses table with a child table that contains the required reading for that course. I also have a student table that contains which course the student is currently taking with a portal to the required reading table for the selected course. What I'm trying to accomplish is a way to select which of the required reading titles have been completed for the selected course and student. Not sure if I'm approaching the correct way, stumped :-( Environment: Hosted file on FileMaker Pro 11 Server with Mac/PC FileMaker Pro 10 clients. Thank You Anthony Students-Courses.fp7.zip

Hello, My current project is meant to manage Deviation reports. The Deviation report is like a lenghty form with several sections, each to document a particular step in the Deviation Management process. Since it is necessary to document which user fills in which section, is it possible to implement a sort of Electronic Signature with FM? For example, the first part of the lengthy Deviation Report is "Deviation Identification and Submission". After this part has been complimented the deviation reporter, let's say user 1, needs to sign it electronically and send an electronic notification of the Deviation Submission to another user 2. Then, user 2 sees the De…

My problem is I can only get some fields to update by an extra click on the page. Data structure is. Tables Patients (one to many) Episodes (ONe to One) Organisation I am on a Patients layout and using a drop down list to pick Organisation. EG PAT::pk = EPP::pat_PK to get to the episode EPP::ORG_fk set from a drop down list where EPP::typeORG = ORG::typeORG Sets the fk fine but wont update ORG::Name on the patient layout without an extra click. I have tried messing with script triggers and read some stuff about using portals and overcomming the problem of two layers of relationships. I hope I have explain…

In one layout, I have a formula that auto enters a serial # and assigns it to each new record. The layout contains a portal to another table that I would like to link to this serial #. Is there a way to make the contents of the portal saved as it's own record automatically whenever a new record is created in the main layout? They would be related by the serial number. I want the portal record to be all automatically filled out and not directly editable by the user. It's just a "record" of what was entered into the main layout.

I'm a relatively new FM user but have been forced to hit the ground running and am doing my best to learn along the way. This particular problem is giving me some trouble. In generic terms, I have several fields in each record that have multiple possible responses (e.g. "Completed", "Incomplete", "Pending Data"). I would like to keep a running count of these responses for all records in the database - one for each field of interest. So basically, I can already look at the summary page for each individual record and see which tasks need to be completed, but I also want to be able to look at the database globally and use the existing data to say "X number of records s…

Something weird guys. I've been working in FM for a few years now, I started with 8.5 and learned early on that relationships don't work if the implicated fields are calculations that reference fields in other tables. Recently, a colleague of mine who is new to FileMaker and didn't know about this restriction has made a few relationships using calculations that referenced other tables, and the weird thing is they WORK. They don't work with the get related records script step, but portals seem to work fine, and scripts that read values from fields in the related table pull the correct related record value. Does anyone know if there is an official e…

I have a check box that contains the names to several different companies we send faxes to. What script do I use to connect a check in one of these boxes, to a phone number? Thanks

Is it possible to add a new record to the parent table and then add a new record to the child table and then add a new record to the grandchild table from inside one layout? Parent: Students Child: Admission Grandchild: Agency Admission I have another table called Agencies and the Agency Admission is a join table between Agencies and Admissions. One student can have multiple admissions and multiple Agencies can be involved in one admission. Any help is greatly appreciated. Thanks.

I'm trying to setup a backorder system for my invoices. I now have an invoice table with a related list items table for use of a portal on my layout which actually displays the invoiced parts. On my invoice layout I create a new record and on that record I have a portal in which I add the parts ordered and delivered to the customer. The portal works just fine and creates new records in the list items table. In the list items table I created a calc field wich uses qty ordered and qty packed to calculate the amount that's left in backorder. I want for each record that gets created in the list items table to create a new record in the table Backorders and fill …

I have been playing around with a design pattern utilizing a table for storing layouts with everything else buoyed out. My question is in regards to performance. If I have 80+ tables with a few running several thousand records and growing Is this a bad idea. I would have less tables on the graph but more loading I presume of unnecessary tables on layouts that don't utilize them. Anyone, think I should avoid this strategy? Some layouts will have a thick patch of Tab Panels too, does related information get requested only when it is viewed? Not sure what goes on under the hood, some fead back would be greatly appreciated.

I'm having real problems getting a portal system to work. Even when starting afresh from a base perspective I can't seem to get the relationships right... which seems to state that I have missed a fundamental step somewhere... but tracking it down is driving me crazy. My Aim: Small database saying which primary contractor worked on a particular job. 2 Tables. - Customers & Contacts 2 Layouts: Customers with Buttons to delete/add Customer and a button to view related contacts on the contact Layout. Contacts with a portal listing the contact type (Bricklayer, Surveyor, Painter etc) + Button to delete selected portal row contact. Detail section.…

I'm trying to make a form letter, but I want to include multiple records to my form letter so as there a way to get filemaker to do this like: Dear <<First Name>>: Our records show you have the following items missing from your rental return: <<related record line 1>> <<related record line 2>> <<related record line 3>> <<related record line 4>> Please return them as soon as possible...

We deliver parts and we manually create the delivery stops now. We have dispatch who creates each stop of a run. We time how long it takes between two points (called two Stops) and marry the two stop fields along with Time in a Travel Times table. I am trying to automate it and hopefully have it create the run line time sequencing automatically. It seems to work except for the first record and I can’t figure out why. I want the first record, if it is the first record, to grab the start time from the Pull Out Time in the Run record what you call parent. otherwise use the prior Run Line’s record’s end time. I hope this is possible because it looks like it will s…

Ok. I understand relations sort of but I don't understand look ups. I have two questions regarding my new file. if you would be so kind, please look at this file on the orders page and explain why the dollars fill in even if the quantity is empty. Second and main reason for post. In Products I will put two prices (each and case) and then case quantity (says whether 12 or 24 to case). Cases are cheaper. Boss will occasionally enter discount on a product which will run until he removes it. Look up on the discount and each price works. When someone orders 14 of a product system should enter 1 case and 2 each so they get the best price automatically. I need…

Ok, Question for a guru or regular person who understands this topic more than i. say for example: custID ---|| orders(cust_fk) simple equijoin - cust id = cust_fk , we have a relationship - when data is entered on customer context, HOwever , when in orders context, we have to allow Create a relation. Magic Keys, scripted key moving data back & forth can transfer keys, but how would i make a new relation without having that allow create in child table selected or is this just not possible? thanks

First off, this is the sequence/relationship of my tables. Ingredients --->FormIngred (a join table) --->Compound Formula ---> Audit Form The Compound Formula table contains a bunch of formula's, these formula's can essentially be "copied" (i use this term very loosely) and an "audit" is created. The audit serves almost like an invoice in that it records each time a certain formula is made. The problem is that if a formula is changed in the compound formula table, these modifications affect all the audit's that are connected to that particular formula. This cannot happen, audit's that already exist cannot be modified even if its related formula…

Hey gang again!! screen shot of erd wanna be.. problem with this as i am designing is too many exceptions with allow creation,etc. so soon. scenario Customer walks in shop. they choose to order a pull apart cake. IN order for that to happen we need. customer name, etc order id # due date, due time, message, notes, kitchen notes, message, flavors, style, pa-type, delivery status, icing ( i would love to have an @ATTRIBUTES table as other items need this same information. ) Putting all of this in a portal doesnt make sense to me as we have other items such as cakes(buttercream, creamy fab, fondant ), cupcakes(single, dozen, (@Attributes …

Im working on a recipe database and am coming up on the last phase of the project and am drawing a blank on how to accommodate the request. I need a layout to compare information from 2 tables. The tables are: 1. Goods_In_Ingredients 2. Production_Ingredients. Im not sure how to setup a relationship for this scenario or if it is even possible. Here is a bit of details outlining what i'm trying to do. I need to have 2 drops down lists on the layout. One for "Ingredients" and one for "Batch_Code". Based on what is selected from these drop downs, the following needs to happen: 1. All of the items in the Goods_In_Ingredients table that have the i…

Dear All, I am a newbie and have a tricky 2 "stage" find question. How do I create a script that will find results in a table based upon the results of a Find in a different table? Specifically: 1) Out of 10,000 shops, how many of these sell "apples"? These shops are in table <shops> each with a unique ID. "apples" are in the <fruit> field (I can do this one!) 2) Based on these X number of shops, I want to know who are the suppliers to these shops. These are in table <suppliers> and each supplier is linked to the shop through the unique shop ID. Therefore, I want to create an extend find set from the <suppliers>…

Hello, Sorry to put this in Value Lists, since it is much broader. I wish to build a file that lists what nurse works for a particular doctor, and what facility they work at. A doctor can work at multiple facilities, and a nurse can work for multiple doctors but only one facility. It would be super great if I could pull up a facility, and have it list which nurse works at that facility and for which doctors, or pull up the doctor and list what nurse works for that doctor and what facilities they work at, or pull up the nurse and list what facility and doctors they can work for at that facility. I have no clue where to begin! TIA edit: Maybe this would help som…

Good Evening! Would appreciate anyones views on the best way to maintain a customer cash account within filemaker. The idea being that a customer can deposit a specified amount and then when each invoice is generated, it will deduct the invoice amount from the account balance. There would obviously need to be the option to 'top-up' the account when needed and also to generate a report which shows the breakdown of account usage and when the account was topped-up etc Currently have the basic database configured in a similar manner to the default 'invoice' template which comes with filemaker - Customer, Products, Line Items and Invoice tables... …

I am new to FM and have purchased FM Pro 11. I am trying to use 2 files and join them. I just read that I can not copy a table from one file, but I can import it. I can not seem to find where to do this. Any help would be wonderful.

Hello Everyone, Just trying to get some Helper text to show in my fields to help users with interface direction; what i would like is to say have a fields where they can see in the field something like this: please select flavor here : filling type but when they click in any option, all i see is the field and that text is eliminated. Is this conditional formatting or just floating textfields. i hope this makes sense and someone can help. -i

Hi: I´m about to start a project for the company for which I own a franchise, is a fast food mexican restaurant. They sent me their inspection form and to sum it up is something like this. Quality Meat had required temperature: 15 points Meat looked the right color and looked correctly cooked: 10 points Turkey had required temp... : 15 points and goes on with all ingredients and items. Then we have another section out of many others like: Appeareance Restaurant is to standards: 10 points etc....... Customer service Client 1 Hospitality 10 points Preparation time 10 points and many other items, …

Hello, If anyone can point me in the right direction with this it would be greatly appreciated. Basically I have 3 tables: clients, suppliers and transport orders and what I want do is have a drop down list on the transport order layout that shows a list combining client names and supplier names. It is not important that the list shows whether the person is a client or supplier it just needs to show the names. I have been trying to work out the relationship but with no success. Thanks James

Good Day Guys, I need some assitance with a FM11 Template that I want to use but modify slightly: The Lending Library File. I am a new Filemaker 11 user. The lending library has 2 main Information tables, (1) Assests & (2) Contacts. Once an item has been booked out to a user, this item will be displayed under the "Check Out History" tab. My request or query is this: How can I change the template as to allow me to click on that listed Asset (in the "Check Out History" tab) and be taken straight to the Asset page with all the details of the Asset? - See attached image "Lending Library 1.PNG" Any assistance would be greatly appreciated. R…

Hello everyone; i am a bit annoyed that i just dont understand the join table from a tutorial that i have been reading. It is an invoicing solution which is great, but the instructor doesnt really go into depth explaining why certain things are in the invoices table & not in the line items table. Can someone please explain to me why you would have: price - *main data in invoices table ( lookup in lineitems table) qty - only in lineitems table , not in invoices table extended price ( only in line items table) total of invoice - (only on line items table) i am sure someone can explain reasonably simply what the join table(s) purpose is and wha…

Hello, From a design point of view, in a set of related tables within a database: is it recommended or optymal to have multiple join files or a single one. For instance, given: teachers students classes teachers_exams courses We could have: teachers JOIN1 classes students JOIN1 classes teachers JOIN1 teachers_exams students JOIN1 courses teachers JOIN1 courses or teachers JOIN1 classes students JOIN1 classes teachers JOIN2 teachers_exams students JOIN2 courses teachers JOIN2 courses Thanks