Relationships

Okay, so I have a relatively smooth running database here, but the design requirements just changed and I'm wondering how to go about this change. The way it stands now: Client-- Each client can have many programs, and the programs can have many inputs. Now, I need to modify the Client to Program relation. Each client can have multiple programs, but any certain one program can be used by one or more of the clients. The way I have it set up now is that the Client ID matches to the client ID in another table, which is assigned to a single program ID, and then all the inputs have their corresponding program ID's. Realtional databases kinda confuse me, a…

In other programs I use I can add "groups" or "relationships" that can help me figure out which of my contacts belong to specific organizations (this could be another table, right? for each organization, one to many) but also who knows whom, who's related to whom... which become strings.... Do I do this with drop down lists, repeatable fields (like Apple address book)? is there a way to map these relationships? tia

Am I Missing something here? Here is my issue, I am showing a portal in my checks table. the portal is based on the invoice table and should show all the invoices that still have a balance. The only way to automatically do this is to have the relationship based on the calculation field of the invoice balance. If balance is zero or less the record should not show in the portal. Once I make a relationship like this the portal shows no related records. -Don

I'm not sure where to post this question. I have three files; patients, lab dates, testresults. Each patient can have many lab dates which can in turn have many tests. They are related this way: Patients _kP_Patient ID Lab Dates _kP_LabDateID _kF_PatientID TestResults _kP_TestResultID _kF_LabDateID I want "see" from Patients the last set of test results, that is, the testresults that would have the greatest labdateID for that patient and capture them in a value list. If I create in patients a calc field MaxLabDate = max (patients::LabDateID), I can't use this in a relationship bcs it doesn't index. I remember back in FM5 days a technique…

Hello, Wasn't quite sure exactly where to post this topic, so if it is incorrect, I am sorry. I'm trying to set up a Purchasing Database. 2 tables Purchase Order PO Item Purchase Orders mainly cover 3 things for the company I work at. Direct Material, Labor, and Consumables. What I have done is each Purchase Order can have many Items but PO Item can have only one Purchase Order. The Problem comes in with these 3 categories. I have 3 layouts for Purchase Order table, one each with a portal that allows creation of records into PO Item. In the PO Item Table, I have created fields for each category. Example; M_qty, L_qty, and C_qty. This way, when…

I hope this is the right place for this: I have seen this functioning in Access whereby a record has a history. One value changes - say, property assessment value - and I want to keep multiple copies of the same record which allow me to track the changes in that value. So I need to group the versions of the same record in some way to show a 'history' of that record. This is probably straightforward, but I would like to know how to organise this before I go down a particular track.

Hi all! I'm trying to set up a customer file. I'd like to let FM handle as much of the work (of creating records) as possible, using relationships between various tables. The requirements are as follows: • each customer is unique (well... of course!!!) • each customer may have several phone numbers • each phone number may point to several different customers I thought of using three tables: Customer, Phone_Numbers; to which I thought I would add a third table (say "Cust_Phone_Numbers") that would just be the link between the other two tables; and only contain the keys to the other two related tables. I'd like to be able to see, for each customer,…

I'm creating a dababase where teachers submit test questions. Often, they submit just one question and thus are assigned a submission number. They may, however, submit one main question with a series of subquestions, in which case they are assigned a common submission number plus a sequential series number. For example: 1 2 3a 3b 3c 4 Would this call for: a. A single table b. A single table with a self-join (key = submission number) c. Two tables Thanks!

Simple and maybe easy for most of you. I am trying to get a databse that has 16 multiple fields and need to combine them into a field with 16 repeating fields. Please send help.

I am having trouble getting related data to show up in filemaker on a layout. The layout uses the table Baan for individual records. BOM baan Connection is a table that lists all the items from Baan but lists all their information. The tables Display Formula, and Display Packaging are used to seperately display and seperate the formula and packaging information into portals. All of that works fine, but when I place the Country name from the Country table on the screen I get a different country code for most of the items. So for item number 410002001 It should be CN for the country code, so China. But the code that shows up is CA on the screen and Canada …

I have two databases linked on account number. Table 1 stores payments and Table 2 stores address information for the customer. I am having a problem with some of the fields being deleted in Table 2 when a payment record is entered into Table 1. Some Table 2 data should appear on Table 1 once the record is committed though iwp, but I am only getting the customers name to appear and the rest of the data is deleted that I am wanting to appear on the form for Table 1. Any ideas why this is happending? Thanks, Scott

I could use some help designing a database. I have some FM experience, but am not an experienced developer. The database is for managing collections in an archaeology lab. I have the basic design ok but there is one complication I'm not sure what to do with. We have accessions, material that comes into the lab by donation or a project we do. These are stored in boxes. Each box has an ID. Within each box, I have box contents (like items on an invoice). A box can have contents from multiple accessions or have multiple items from one accession. Right now I have tables for Accessions, Boxes (with ID and location), and box contents (with relations to accessions and boxes…

Hi, I have a table of images - reciepts - related to a table of expenses. Some reciepts are related to more than one expense account and therefore more than one expense (one account can be reimbursed by another for certain expenses). I've created a "filtered" layout which shows data from the expenses table via a relationship which matches the image name from the images table to the same name in the expenses table. When I do a search for related records using a given account, the "payment numbers" in the records shown are not all from the account I've requested the search from. I know this is because of the way the related records are being sorted (there is…

Hi, I have an invoicing solution for training services. I have also 2 files, Clients (i.e. payers) and Students (attendees). Sometimes Clients will send an employee and pay for the training. Other times the student will pay for the training. And other times, the Client will pay half and the student will pay the other half. I can get FM to create invoices for either table (using for example c_InvoiceTo defined as ClientName & StudentName), but the question is should I? or is there a better solution? Until now I have always thought of these as 2 distinctly separate groups. But the problem with this is if I make a Student also a Client, I have to track v…

Hoping someone can help me what should be a simple thing? though I cant find any threads that help me out with this specificaly Am new to FM though am accustomed with relational database design To keep it simple - I have two tables: 1) Matters 2) Matter Types The matters table has various fields relating to the matter. What I want to be able to do is have a drop down list on the matter layout so I can pick a matter type from the matter types table. Now I know how to do this through creating the relationship between the two tables (on MatterTypeID) but I cant get arround the drop down list displaying the MatterTypeID once selected. I want …

wondering if someone know a way to do this. I have a many to many relationship between a matters and clients table. I am displaying the clients for a matter via a portal on the matters table and am using merge fields to display the data "nicer" (eg Surname,Firstname rather than two fields that will likely leave alot of white space) So in order to add clients I'm using a lookup layout for the user to select the one they want. Is there a way I can query the contents of the junction table before adding a client to make sure their not allready associated with the matter. I figgured I could add a loop in a script and check each row of the junction tabl…

I have a table that have a buch of fields including an ID_pk field. I want to create a self-join so I can create a portal based on a field named "stop loss". So I created a relationship and Named it Stop Loss and connected the stop loss fields. Clicked OK and opened a portal and the relationship was not listed. What's up? Help Robert

I have a relationship that shows records via a portal. Basically shows what machine a job ran on and when. So my relationship say when the equipment and date match, show the records. Iwant another relationship that will sho when a job shipped, so the ship date and status(shipped) must match to show the records through the portal. When I do either of these alone, they work fine. I am having trouble creating the two separate relationships and assocaiting them with different portals. Help is wanted.

On several of my posts, the responses given referred to Table Occurences... something that I know nothing about. I don't know how to create a table occurence or when/why I would need one. I have attached a screen shot of my relationships, and if you could provide any feedback, I would really really be grateful. Thanks in advance for your time and honesty.

Hey GUys/Gals I created an enrolled_patients database and a subject_visits database. The two are joined by the subject_id (single-join relationship). The enrolled_patients database is pulling in data from the subjects_visits database. I created a calculation in the enrolled_patients database that says If the enrolled_patientssubject_id = subjects_visits::subject_id and subjects_visits::visit='SCR' then add an 'X'. I've created the same calc for visit='RND', but I can't see both. The problem is, depending on how the subjects_visits::visit is sorted, I won't see an 'X' on my calculation. I need to see all related visits by visit in the enrolled_patients database. …

I'm a web developer with a client that is using Filemaker. He gave me a set of Filemaker files that are empty that he's designed over the years and wants to use it. One major problem with this configuration, though, is that he's using the phone number as the ID for customers which pulls billing/shipping information into the invoices based on what's in there. I might as well explain in detail... Here is a look at the customers file: http://angelleye.sytes.net/dealsdirect/temp/customers.jpg Here is a look at the invoices file: http://angelleye.sytes.net/dealsdirect/temp/invoices.jpg Now, there are 2 things I don't like about this and they both kinda go tog…

Hi guys this is my first post i hope i have done it in the right place. I have a database for a real estate company in spain which relates to the houses that are for sale. This i have built up myself. What i want to be able to make filemaker do automaticaly is when a Corredor is selected filemaker will look to a seperate table with the Corredors in and collect their letter identifier and then increase the number next to the letter by 1. Thus creating the next correct identifier for the house, this cannot be changed now it must be a letter that relates to the correct Corredor and the next number for the property. I hope this maker sense! Does anyone know how i might do thi…

I have two files: Jobs.fp5 and TimeTrack.fp5. The relationship is based on a JobNumber field (Acct #-Month/Year). When I open a client's document from within Jobs (using AppleScript), a new record is created in TimeTrack with a time stamp. When the document is closed, TimeTrack logs the close time. I have an elapsed time field which is just CloseTime minus OpenTime. It is possible that each client will have multiple records in a given month. I would like to somehow summarize those records and display that total with the corresponding record in Jobs.fp5. Sorry if this is a rookie question, but I can't seem to figure this one out.

Just when I thought I had a handle on it, I realize I am more confused. I have the following tables: People Contracts Job Info Employee Type Employee Subtype 1 person can have many contracts and job information (as contracts or job info change, it will become history). Relationship is People.emp_pk to job.emp_fk Job.jb_pk to EmpType.jb_fk EmpType.emt_type_pk to EmpSubType.ems_type_fk I am setting up a layout for data entry that has people info, then job info (ie hire date, position title) Part of the job info is the Employee Type (ie Faculty, Staff or Student). So I have a drop down/value list for that. The Employee Type fi…

I've read in several books that every table must have a primary key, but I've also read that if a join table has two foreign keys that uniquely identify each record, that a separate primary key isn't required. I believe that by this, it is meant that only the two fk's are needed, and not a third field that is a calculation -- concatenated text field of the two fk's. Just wanted to make sure I understand this correctly. Ken

hello. i am building a Filemaker Pro database as a volunteer for a non-profit children's theatre. I was making good progress. Then my layouts suddenly turn up with "table not found"in every field. Yet, in the define database the tables are visable to me. Are they really gone? I don't know how they disappeared. Perhaps I deleted something somehow??? Is there a wayto recover this (I tried using the recover tool, but without success. I was given a duplicate database which was labeled as recovered, but the tables were still missing from the layouts). I really hope I don't need to begin all over again. Any suggestions??? Thank you! Also, I hope this…

Is there a way to report over multiple time periods? For instance, this week's sales vs. 1 week ago vs. 2 weeks ago vs. 3 weeks ago vs. 4 weeks ago? I am using filemaker 5, 5.5, and 6 on Mac OS 9 and OS-X Thank you.

FM6 was no trouble, but how do I perform a look up from one FM7 file to another completely separate FM7 file (not tables within a FM7 file)? I have a "telephone log" FM7 File and a "project file" in FM7. From the "telephone log" file I want to look up and copy over from the separate "project" file the project number, description, etc.

Hi All - I have a possibly corrupt DB that exhibits this problem: when I search on the "bill-to" orgainization name in my Invoicing database, the search hangs... forever. I have to force-quit out of FM to recover. In other words, the when I search on the realted field, the search fails. I found when I replaced the relationship with a new one, that does the same thing (i.e Bill-To ID in Invoicing matches Bill-To ID in that Invoice), all is well - the searches are fast. But, to avoid having to hand-replace every field that used to use the old relationship with the new-relationship field, is there a way to 'refresh' the relationship in place? See, if I jus…

I am trying to understand how to populate a join table. This is a very basic, newbie question. TABLE: Composer FIELDS: pk_ComposerID (Auto enter SN) ComposerName TABLE: Songs FIELDS: pk_SongsID (Auto enter SN) SongName (JOIN) TABLE: Composer_Songs FIELDS: fk_ComposerID (relationship: many to one (pk_ComposerID)). fk_SongsID (relationship: many to one (pk_SongsID)). Composers can write many songs (actually compositions; I'm using "Songs" to make the example easier to read here.) Songs can have more than one composer. Since this is many to many, I create a join table. Goal: Be able to track what songs composers have written, and …

I am making a new database so that I can see how my stocks are doing but Im not sure how to import multiple days stock prices into the file. I want to be able to see if the stock has been going up or down over the past few days. I know this can be done with tables or relationships but I do not have the Filemaker skills to figure out how to do it. I have attached the file Im working with loaded with an example. I am stuck. stock.zip

Hello! I am working on a property management database and wondered if anyone can help me figure something out. What I am doing is making a "tenant roster" or a list (view as list) of all of the tenant records. In the roster I want to be able to select a month via drop down (know how to do this). So now the MOnth Select field reads "January". In the same table I have 12 fields corresponding to 12 months from now which is a payment matrix and holds a year's worth of amount they paid per month. So for the first record, a tenant that started paying rent this month, field 1 is April, field 2 is May, Field 3 is June, etc. The tenant that starts next m…

I have a table of service rates. There will be two type of records. A global and a custom. These service rates will be used for creating service orders. When a line item in a service order is created it will lookup up a rate from the table of service rates in relationship to the account the order is for. But not all accounts are charged the same which result in a custom rate in the service rate table which has a foreign key to the account table as well as a foreign key to the global rate record to maintain the description. So... I would like to have the service order to show all master rates that have not been customized along with the account specific custom ra…

I am in the process of creating a database for my estate agency business. So far I have created two tables. One headed 'Properties' and the other headed 'contacts'. From the contacts layout I want to be able to view properties through a portal with matching multiple relationship critera. I aslo want to be able to view related contacts via a portal from the properties layout. Should I be creating a third 'bridge' table? Am I living in dream land? is this possible? Is this a many-to-many relationship? Any help greatly appreciated! JC x

Here's the issue. I have a planner database. I want it to pull info from the schedule database, banquet database, and entertainment database and display it on all on the calendar in the planner, very much like iCal. It already shows information from the banquet database, but that's all. I am using cc calendar's free edition and molding it to my needs. Is there a way I can get it to pull dates and information from all three databases and display it on one calendar? If you haven't seen it, the way it displays events is through portals. All I really did was replace the events tables inside the cc calendar with TOs from the Banquet Database. Sorry for being so longwinded…

I am working on a database for donations. I have a contact table which holds each donors information. I have a donation table to hold the donations and a donation detail table. The contact ID is the key field in the relationship between contacts and donations. The line item ID is the key field in the relationship between donations and detail. Each donors can have many donations and each donation can be spread over many accounts. What I would like to do is design a donation entry layout in the donations table. I have pulled the Contact ID, name, address in by putting the fields from the contact table in the donation layout. My problem is this, when I add a ne…

I have a database that uses multiple tables to display data. The key field in all tables is one called Symbol (as in ticker symbol). Each display panel contains fields for one specific symbol. What I want to do is maintain continuity from one table to another when looking at the data for a specified symbol. In other words, if I'm looking at a record on one table and I click on a panel that displays data from a different table, I want it to display data for the same record (symbol) I was looking at in the first table and display the related data from the second table. I hope this is clear... :qwery:

I have a report that shows case sales of multiple items, each with a unique upc code. By unique I mean, some codes have only the second half of the upc code and not the first part. Others have a alternate 3-5 digit code altogether, and are much shorter numbers than the upc codes. And some of the codes are formatted just right. I created another table that has the alternate code numbers corresponding to their appropriate and properly formatted upc code numbers. What I want to do is to have all the upc code numbers properly formatted for the report and also to show total number of cases sold by upc code. So I figure I have to compare the upc codes in the original…

My husband and I own our own trucking company. We need to track the states he travels to and how many miles he travels in each state for tax purposes. I have set up a database to record his trips. Using repeating fields (because that is all I know how to use - I have never used a relational database...maybe that is what I need, but I am not sure), I have set up my db to be able to input state and miles per state for each trip with a total at the bottom of the miles column that adds the total miles for all states traveled on that trip. Example: for trip #1 = CO 70 miles UT 90 miles WY 10 miles total 170 miles for trip #2 = CO 80 …

Let me eleborate. I'm working on a db design, and so far my ERD has 19 tables, 10 of them join tables. The reason is I initially have lots of many-to-many relationships, like coordinators being 'in charge of' one or more courses at any given point in time. If the courses are thought of as being only ones offered only during any given academic quarter (e.g., Spring 2006), then one coordinator has zero, 1, or many courses. But if the course is thought of as a course instance, meaning course X has a record (in a 'course' table) for every academic quarter it is offered, then the relationship is many to many. Based on the latter scenario, I sketched out a join table which is '…

Hi! I am building a property management database. There are 2 tables -- the Property Information and the Tenant information. the Property Information table holds all the master info about a prop. Owner, mortgage, insurance, etc. In the Tenant Info I have a value list set up, generated from the Property Info table, which automatically lists in a drop down each property address from each record in the Property Info table. So if Tenant Joe lives at 1725 N. 3rd, I just go to the Property Address field and select 1725 from the Property Info Value List. Now's the tricky part. Once I select 1725 on the Tenant Page through the value list, I now want to autopo…

I’m working on a solution and I need some suggestions as to where to start. I have a customer base of about 150 accounts. And a product line of about 3,000 items. The customer accounts are broken down into four groups to identify a specific pricing group, A-D. Each group has its own selling price, across all products offered, identified in each customer record, i.e. account number, name, address, and pricing code. What I want to do is to be able to reference, at point of sale a product portal specific to that customers pricing structure. Where my sales staff can identify the customer and the portal would list all products with the appropriate selling price. My questio…

I am trying to design a solution that will be used to manage events from year to year. I have a number of tables that have unique information per event but not unique in total per table. The item table, the attendee table and the catalog table are all related to one event however I don't want to build new tables per event. I could do a find by event and then sub find by the needed item but I thought it would be better to be more specific in my design to over come the issue. In short for each Event I want to use the same bidder numbers, the same catalog numbers, and I only want to see items that have been collected for a specific event. I need to be able to ru…

Hi all, Hope there's an Einstein out there for this one. I have a jobs database. There is a job layout for each er, job. On that page there are buttons taking you to a job end sheet and contract. Now using the contract layout page as an example at the moment when I click on the contract button it takes me to the contract but it's the last contract I did not necessarily linked to that job. I want to link the Job layout to the Contract layout (& Job End layout) so that those pages are related to that particular job. Essentially, each time I create a job 1 job end & 1 contract page are created too making them distinctly related to that job. phew! …

I have four tables. The first one is titled Consumer. The others are Payments, House, and Vehicle. I've attached an image to show the relationship. My question is, If I wanted to do a search of all the payments a consumer has mad across all TO's, HOW DO I DO THAT?

I have been an avid reader on this forum and other help sources (books, the white pages, anything I can get my hands on). One thing that has constantly stumped me is the discussions on properly setting up your database in regards to invoices and how they pertain to my business model. For example I've kept this comment in my files: ""When printing an invoice, you don't print from the invoices file, because FileMaker didn't design portals to be printed. Portals don't slide in all scenarios and don't page break gracefully. Instead, you use the Go to Related Record script step to locate all the related records in the line items file and print from the line items file.…

I need to be able to store information in one table based on the relationship permanently. The two tables I’m working with are Users (Employees) the other table is Requests. Right now using a simple relationship based on a UserID I can show the User info in the Request table no problem. But, if the employee record is deleted from the User table then the record in the request table is missing info. One way I've gotten around this is to have fields in the Request table to "lookup" the user info and store it that way. However, this doesn't allow me to Find user from the request table. Is there technique that I can use that will allow me to store the user inform…

Using the separation model, so have clients table in one file and client calculations table in another file. When a record is created in the client file it generates a serial number value in a field called Client ID. Client calculations table also has a client ID field. This is the match field for the relationship, ive ticked the box 'Allow creation of records in this table via this relationship' under the client calculations table. I thought this would mean that when i created a record in the client table a new record would also be created in the client calulations table with the same ID, but it doesnt work. What am i doing wrong?!

Hello, A create a database for recipe management. I do not find how to integrate my sub-recipe in my table product. Also, I have difficute with to link my external table "ingredient" and my global "produit_tout" I would like other comments on my work Examples are better than my bad English In file attachements 2 versions Thank you for your help and commentary, Louis Standardisation_modifi__3_.zip Standardisation.zip

I'm skethching out an ERD for a solution in which multiple (at least 2) tables, will need to reference a value for a specific quarter and year, e.g., "2006 summer", "2006 fall", etc., for the purpose of creating join tables where instructors and educational specialists are linked to occurrences of instructional 'courses'. My plan is to create a value list for the data (e.g., "2006 summer; 2006 fall", etc.). The value list needs to exist in some table; since more than one table will be referencing it, shall make a table for this data, and relate it to the tables that will need to reference it? Thanks.

I have two simple files that were converted from Filemaker 7 to 8. One relates to the other via a single field, and to itself via a Self-Join for Find Duplicates. I am trying to adapt these two files to a slightly different purpose by changing their names, table names, file relationships and updating the relationship accordingly. I have tried several different ways of doing this short of recreating new database files, but cannot get around a Table Missing error in all fields of the database holding the relationship. Recreating the relationship does not solve the problem. I have checked and rechecked without finding an error. I have a sense that the problem…

Hi! For some reason I can't seem to be able to enter text into edit boxes that I placed on a layout. I get a message saying "Before typing, press Tab or click in a field, or choose the New Record menu command." I created a new record and nothing, I know this is probably something very small that I am over looking, but I could use some help. thanks

I have a job record file that I need to create invoices for. I also have separate file for invoicing. I want to click a button in the job file and create a new record in the invoice file. Do I do this a a script?

Hello, I am creating a database for by business (estate agency) and have created two tables "Properties" and "Applicants". The idea as you might imagine is to match properties to applicants. All is well so far except for one thing: In the properties layout I have a portal displaying a list of matching applicants who may like to buy the properties BUT I cannot figure out how to put a check box relating to each of the properties inside the portal relating specifically to that appplicant. I need a way to remind myself that I have contacted them about that specific property... Is there a way? Someone must know...

So, after making a functional database, and then putting it to use, I showed it to a programmer I know. I had put ID numbers on most things, but it looks like I didn't use them properly. The database is linked by text name, not the id number. Therefore, in a projects tab, I have the customer name for the project linked to the customer name. From what I hear, this is bad design, and I want to go back and fix it. So, I created a field in customers for ID. I want to link this to the projects table. Can I link it to the company name field, or do I have to create a customer id field in projects so it relates to customers properly? I can do the latter, but…

A business sells clothing that has: different clothing items and different color choices. Some color choices go with some items, others with others, with overlap. There is a file that stores the color information. Only some of the entire color choices are used at any given time and from season to season the available color choices may change. E.g. in 2004 for “Blouse” the colors 3, 6, 9, 46 and 77 are available, for “Skirt” 2, 3, 7, 22, 45, 46 … etc. In 2005 “Blouse” has color choices 6, 9, 14, 45, 55, 57. etc… I have set up a static COLORS file that contains the full set of available colors for any item in a static MERCHANDISE file. This is accomplishe…

Hope I'm in the right forum here. How can I have a relationship where the master file can display a value that's computed by an average function of values in the related file, with the following conditions: - This average must ignore omitted records in the related file, even if some of these omitted records have the same value in the match field that the found records do. - Records in the master file that don't match any of the found records in the related file but do match omitted records in the related file, should not display the average of the found records in the related file. - In the master file, e/ record has a unique value for the match field. In t…

I enabled Instant Web Publishing for my db, but the relationships do not function properly when people access it online. Is there a problem with Web Companion and relationships? Or is there some preference or work-around?

I am struggling using the not equal to (NEQ) relationship between tables. I have a simple 3 table database (attached). Forum - a list of forum which members may join Participant - a join table, showing which Members have joined which Forum - Forum may contain multiple members, Members may join multiple forum. Member - a list of all the Members. I wish to show a layout for each forum which lists in one portal those members in that forum, and in another forum the balance of members (the pool) that have not joined this particular forum. I thought the latter portal would be an easy application of the NEQ relationship - but the results are unexpected. If a foru…

I have a table called Inspection. It stores suite inspection information from related tables (Property, Suite, Staff) and user input. One Suite can have many Inspections. Now, if I do next inspection for the same Suite I need to see previouse inspection date on the form. How can I do this? Thanks pm_test.zip

Hi Everyone, I have a simple database in MSAccess that I need to produce in Filemaker. The problem is that the fm database does not allow me to filter records from one table, based on the selection in another. I have included a version in Access & Filemaker. I am probably missing something simple. I would appreciate some help. Thanks, Locks.zip

Okay. So, the solution to my problem is probably very simple, but I can not figure it out. I am working with the sample Contacts solution that comes with FMP8. I want to be able to track families with this database. Thus, I want to be able to add one or more "family members" (i.e. Spouse, Child) to each record. Right now, I have each individual (Parents and Children) entered as individual records, with individual Contact IDs (assigned by the database). I do not know how to assign a "Family ID," and make the database work so that: 1. Instead of entering each individual as a separate record, I start by entering the "Head of Household" 2. Then, I enter othe…

Hi All ! I have a filemaker pro 6 database with fm server 5.5 The relationship is as follows: - I have a contacts database and a products database (only about 30 products currently). - A contact can purchase any of the products. - When he/she does this, a record is added to "contact_product_summary" database with the product id and contact id. - I can thus show all products that a contact has purchased Simple so far. - Now what if I want to INVERT the list and show, for a particular contact, all the products that they DONT have ? This came up in (Topic#136734) but I couldnt really understand what they were saying and if it only appl…

Hi, i'm a new member. been using FM for many years but having difficulty with this one. I have a fp5 file in which I create new records for multiples of the same product with a unique serial no. for each. There are numerous products but records for only one product would be created at the one time. Product description and details are looked up from another file. This works ok. I want to enter the qty of product and "auto-create" relevant records. Then, once labels are printed, to lock those records and check the "printed" box. Would like the option to print to PDF if possible (this seems a bit shaky in FM). I would greatly appreciate any assistance in expl…

I have developed a basic solution for "customers," with related "invoicing," "products" and "line items." They all are connected through Customer ID, Invoice ID and/or Product ID and work great. The invoices are typical in that they provide for a part number (which looks up information from the product table), along with product name, quantity, price, discount, line total and multiple product entry (through a portal to the line items table). This allows me to create product and invoice sales reports through the line items table. My question is, what is the recommended way to handle inventory where it will keep a "product on hand" field accurate and does that fie…

You guys have been so helpful already, so here is my next dilemna… My mail order relational database. I have 3 main files: [color:blue]customer, sales order and inventory. [color:blue][color:black]Customer and sales order are related thru the customer ID number. [color:black][color:blue]Sales order and [color:blue]inventory are related by the invoice ID number. Customers have multiple records in the [color:black][color:blue]sales order file for repeated purchases. The field I am looking to calculate is a date range that shows the first sale date and the last, there by displaying the customer's purchasing history. I need to display this in a customer summary list, al…

In my database I have 4 sections...Sales, Service, Inventory and Contacts...everything works but one thing.. On my service and sales orders there is an area for "Product Quantity" (how many purchased or used)...when I input a quantity, I need it to pull that number from my "Amount in Stock" from my Inventory section. If this makes sense and you have a solution, please help. Thanks a lot. - Matt

I have a FMP7 file that has 6 separate layouts. I want to have the ability to take the data that is entered into a record in layout X and transfer the related fields(within the related tables) into layout Y. I'm sure this is very elementary, but due to my late hours and lack of brain power, I'm not getting anywhere. Any help would be greatly appreciated. Thanks in advance... Doug

Hello, I looked around and could not find the answer to this specific question. My problem is a simple one, I believe. I have a table named Customers and a Customer Detail layout. I also have an Orders table and a layout named Orders. I have a relationship setup between primary key Customer ID in Customers table and secondary key Customer ID in Orders table. In many invoicing systems you enter customers and then create orders by selecting a customer from a list of related records. I want that ability but I want something else to happen before you get to that point. I want to have a button in my Customer Detail layout named Add New Order so that when I e…

Most records perform fine. But when I've changed the data in the selfjoin field, it breaks! Fields: GC USN = Number, indexed EVIDENCE DATE SORTER = Calc, text, indexed (concatenates two other fields) dat looks like: 1670-04-14-01, 1670-04-14-02, etc. HEADER EXPORT = If(GC USN here::EVIDENCE DATE SORTER = EVIDENCE DATE SORTER, "first", "") Relationship: GC USN here = GC USN to itself This is simple stuff, intended to create a header for the first record in each set of records that share the same GC USN. It seems to work, but it won't "recalculate." It assigns a Header to, I think, the first record created, then sticks. I've created other fields using …

I successfully created a self joined relationship in order to display all occurrences of an individual patient's records in a portal on the patient's main record layout. The problem is ever since then, any new field I create is related to that field in all occurences for the patient. For example, I create a new field now called "Paid" and Tom Jones has 4 outstanding unpaid records. I want to mark one of these records as being Paid. If I do, all 4 of Tom Jones' records get marked Paid. The wierd thing is, all the fields that I created before the self join work normally. For example, if I write new notes in the "notes" field for Tom's first record, it doesn't …

Hi There, I want to show a list of records in a portal with the following condition: When a invoice is overdue I want to show it in a portal. The problem is that the relationship that is needed must be a calculation that is not saved, because else it won't be calculated every time the date changes. Calculations that are not saved cannot be indexed and therefore cannot be used in a relationship. How can I bypass this problem. I'm using FMPRO 8 advanced v2 I'm from the netherlands and hope that the above discription is readable. It is been I while since I wrote English!

I'm sure I'm missing something very simple. I've a number of self join relationships I use to display records on a custom form when users enter a response in a global field. How do I trap the situation when what they enter returns no related records. I presumed the Get Found Count = 0 method won't work in this case and sure enough it doesn't seem the right method to use. Any help to point me in the right direction gratefully received.

Hi, I'm a newbie working on an equipment rental project, and I'm having some trouble with my relationships (aren't we all?). I've seen a few similar posts, using three tables, but the solution I'm working with has four tables, as follows: Customer (primary key = CustomerID) Invoice (primary key = InvoiceID) LineItems (primary key = LineItemID) Equipment (primary key = EquipmentID) The attached image file shows the current setup of my tables. I'm confused about how to add Equipment data to the invoices, and I basically have two problems (that I know of, anyway)... First, I'd like users to enter an equipment name in a portal on the Invoice la…

I have a solution to keep track of rental properties and can’t quite think through the logic of how to handle something. Let me give you an example of a building with 10 apartments and they are all rented. A. Current tenant moving out: Tenant tells me April 1st… they are moving out of their apartment #412 at the end of their lease on June 1st. B. New Tenant moving in: On May 12th I have a new tenant who will be moving into apartment #412 effective June 8th. (7 days are needed for apartment cleanup, painting, repair etc.) Here’s what I can’t seem to get my brain around. I don’t want to lose all of the past information from tenant A. But I need …

Hi guys. I need help please. As for my first FileMaker Database I am trying to create a simple database for keeping apartment inspection records in order. I need help with the relationships and value lists. So far I have the following entities: 1. Property (can be one or many) each property will have: 2. Staff (can be assigned to any property) 3. Suites (every suite will have one or many inspections) 4. Inspection ( I HAVE A PROBLEM HERE) I need to be able to add/create new Inspection (for the suite) by: 1. Selecting property from Value List from Property table. (no problem here) 2. Selecting suite that related to the previously selected Propert…

I have been trying to setup a relationship between three tables, customer, cars and model. I have setup the relationship between the customer and cars and setup a portal but I am now trying to setup a relationship between cars and models. The end result would give me a portal on a layout called customer where I could select a car make in a portal and then select the model that relates to the car make. I have been struggling with this for a couple of weeks now and just do not know where I am going wrong. Any help/advice would be most welcome car.zip

I've just completed a conversion for a set of datafiles in FP5.5 to FP7 for our FP7 Advanced Server. One of the FP5.5 relationships was defined with the first field of text global field with 20 repetitions as the key to a text field in the related file. The global was set up through scripts so that a portal displayed the single record in the related database as - in that database - the record's key field was unique. Was absolutely gobsmacked when I converted this to FP7 to find that the relationship now displayed multiple records, many of which at first sight appeared to be unrelated. After several hours work I discovered that, in FP7, having numbers in ANY of the r…

I am attempting to edit a built in template in Filemaker Pro 7 - Template is called "Registration". It's pretty much perfect for what I need it to do except that I need it to track payments tied to contacts and to events. I know this should be fairly easy to set up and I know how to create relationships and set up portals but I am confused by how the relationships are already set up in this template. I would like to be able to: -track multiple payments for contacts linked to mulitple events -have payments show up in Contact Registration portal Right now there are three tables set up - "Contacts", "Events", and "Registrations" and there are multiple ID's which are…

I'm setting up a database to track visual effect shots for a short film. The MAIN table has the shot number and a field for the approved version. The VERSION table has fields for serial number, shot number, version number, and a field for approved/not approved. The tables are linked by shot number. I only want the "approved" version number from the VERSION table to appear in the "approved version" field on the MAIN table. There must be a clever way to do this that I just can't think of!

Hello... I am new to Filemaker (ver 8) but have experience with Access and SQL, and I just can't figure it out in Filemaker. I have 2 tables related by Deptid. 1 table is people and 1 table is a list of depts, location, manager etc. I would like to create a layout (as a data entry form) to add a person (or edit) and their dept (and then the location, manager etc would display). I only want the related fields (ie location, manager etc) to display and not be editable. IE - I input John Doe and dept is HR. the HR office and HR manager should appear and cannot be changed. I don't know if I need a portal (I have read that portals are for multiple rows of records)…

I have a table that has two interfaces. The first interface is a layout that should show all records. The other layout is to be based on a table occurrence that only shows records with a particular value in one field. I have tried creating a separate field with that value then creating a relationship btwn the table and its occurrence based on the separate field and the field that may have the same value as the separate field. My expectations are that the table occurrence will only show the records that have the value I want in the specific field. However, I keep getting all the records and sometimes Cartesian results. How should I relate the table occurrence to the ta…

Hi Hope this is not too confusing. I have a table where I can select a field which is a title for the table. Basically I want to have a sequential set of numbers based on the Title. I know I can calculate the number of records with a particular specific title using a script but is there a way using a self relationship so it is automatic. I can see I will still need to include it in a script to make the number permanent but if possible it seems a better way than using finds and record counts. I would also get to see the number before running the script. Thanks

I have a job file that pulls in customer information (name, address etc) from a customer file based on a primary key - 'customer id'. I then have a script to create a new record in another file called 'invoice' which pulls in various info from the job file. But I can't seem to get the name, address etc to come over, i.e. anything that is in the customer file Any help would be appreciated, Thanks! Lyndon

I have a database that is displaying records that aren't related to the current record of the layout in a portal. I will explain the two tables that matter first. I have a BOM Table, and an Item Master Table. There are three types of items in the Item master table. Formula, Item, and Packaging. Each of these has the same information but are not actually labeled as being any of those three types. I have had to use a calculated field to check the first two digits to label them one of the three types. Now, there is a second table BOM that has Item Numbers and then the Formula and Packaging numbers that go along with that Item number. So you could ha…

I have two tables. One of them has all of the values that are used to fill in different fields in the second one. I am trying to find a way to mark the original list if the name has been used in the second table. I have created a complex script which goes through individual records from each table comparing each to perform this, but I am looking for a way to expedite the process because once my database gets larger, this will take forever. I have a relationship set up between the two tables linking 'Species List:speciesname = Database:speciesname'. I want to put an 'X' in the field 'Species List:Vouchered' if 'Database:speciesname exists'. Let me know if this is…

Hi there, i have a database setup that goes like this: there are several different tables. Rather than linking them all together directly, I created a separate table, called "links", which has 5 fields- the table key, and four fields to record the table name and key of the source and target tables. The reason i did this was that i wanted to be able to have a single record be linked to multiple records in a single table. So far so good, and everything was working fine. Then today I noticed one of the portals I set up was displaying additional data. After some digging around, it appeared that it was referencing data from the wrong link. It's probably clearer if i illustra…

I know there are alot of posts out there for calendars and even schedules. What I am looking for is some help in multiple dates per record, placed into a simple weekly schedule. Sort of like a job that gets done in parts through out the week or weeks. I cannot get a script to recognize the multiple repetitions for each record. Only the first repitition is show. Please help.

Just a curiosity: Do you usually use the same table for several contact categories, like for example: clients and suppliers, or employees and supervisors, what about teachers and students, or coaches and players?

Hi, Im new to FM. I trying to make aa database for Freight forwarding. The senario is a customer can request for pickup or delivery with a rate per shipment. in the report, i would like also to sort in terms of pickup or delivery and who delivers it. should i use relationship. what i did i made two tables one for pickup and one for delivery. i tried to sort out but it wont work. i might missing something. can somebody send sample freight database. i would appreciate it very much. thanks.

Hi, Im pretty new at this so any help is greatly appreciated! I want to link a list of consultants and a list of contacts to a list of projects. So these are three seperate tables Projects, Consultants, and Contacts. If the keys are unique for each table, how do I link them? Ideally on one layout you would be able to view a selected project with the specific consultant company who worked on the project and the specific contact for that project. I have read a ton on the subject, but it would really help me if someone could point me in the right direction.

I'm not a filemaker programmer by any stretch but I've used FM over the years for many a project. I've always wanted FM to provide the info below but have had to take it to Excel to get this kind of data. Any help you can provide would be greatly appreciated. I own multiple restaurants and accrue sales data each day for each location. To keep things simple I'll pretend I'm only using the following fields: Store Date Sales I've been trying to figure out how to create the self-relationship and resulting calculation that would give me the Week To Date sales by location. I'm close to success as I'd figured out how to use a serial that references the first …

I'm using FMP V7 on a Macintosh. I have this problem I can't seem to find a good solution for. I've developed this database that tracks the television pitches my company has made to TV networks. The database contains two main tables: Ideas and Networks. In the ideas table, we track the specifics of each idea including the networks we have pitched each particular idea to. In the Network table, we track the specifics of each of the target networks we pitch to like demographics, etc. All works fine except when I try to get the two tables to relate together in the following way: In one of the layouts for the Network table, I want to see a listing of all the pr…

I am having trouble importing from an Excel file. I have multiple tables related to one another. I want to import the information into multiple tables from the same record. When I am in the import process, I am only given the choice of the mail table. I cannot specific fields per record into the proper tables. Do anyone know a way around this? Example: First Name -> Contact Table Grade -> Academic Table Coach -> Athletic Table All three tables are related by an ID field

Hello all. I have a bit of a dilemma. This is regarding FileMaker Pro 6. I have two databases (one new and one with records) that I want to link together. I want to be able to link all fields to the 2nd database and be able to run a find with any of the fields and have it pull up all of the fields. Example, Enter a job number and have it pull from the 2nd database, the location, customer name, etc. (which are all fields located in both databases). How can I do this? I would really appreciate it if anyone could help me out on this. Let me know. Thanks, Miguel

Hi, I have a file of clients, some of which are related. For example client 123, 234 and 345 are all related to each other. I want to set up a display of related clients so that if I enter 234 and 345 as related clients to 123, then I will have 123 and 345 showing as the related clients of 234, and have 123 and 234 showing as the related clients of 345. I am guessing this requires a self-relationship and a portal, but I'm not sure how to set it up. Suggestions?

Hi. I have a solution where i need a to add an authorization number field in a table. the valid numbers are stored in another table. when i create a new record in the master table i need to assign the next available number in the related table that has not been used. this needs to be scripted so i can create a 'Add Authorization Number' button. i assume this is easy to do, but i cant figure it out.

Hi all, been lurking here for a bit, haven't been doing much in Filemaker apart from maintaining my v5 databases making sure they run on 8. I've now decided to port some stuff to 8 and have run into a brick wall. For the life of me I can figure out how to set up relationships in 8 to do what I want. I've tried setting them up the way they in v5 but had no luck, I'm now starting to question my sanity. I'd love for someone to have a look my files and tell me where I'm going wrong. What I have is a set of three (stripped down) files, one Suppliers, one Stock details and one Purchase orders. In purchase orders, once a Supplier has been selected only the items avai…

To simplify the problem i have a cars table a parts table a groups table(to serparte parts like springs, shocks etc) and a parts for cars table Now on my layout(cars table) i have a portal(partsforcars table) But i want to filter the portal so that it only shows certain parts based on the group ID. i have made another table occurence called filteredpartsforcars and i have the realtion ship joint by filterkeys (groupID), but my portal is showing no data? does anyone have a similar problem or know how to solve?

I'd like to build a task manager that does the following: 1. shows my pending "tasks" 2. sorts them by date due 3. doesn't show them if they're marked "complete" 4. allows me to set some tasks to expire if they're not done by their due date, and some to "stick" so that they will continue to show even if I don't finish them. I think I can handle 1-3 but I'm not sure how to approach #4. Could someone help me to conceptualize how to approach #4 ? I'm thinking perhaps the task could have a calculation.... so that if a field named "display until complete or deactivated" was checked, then a calculated field "display this ?" could return "yes" or …