Relationships

"This operation cannot be performed because one or more required related tables are missing." I have a fairly complex database and am using the "Delete related records ..." feature somewhat liberally. What are the usual suspects with this error? I've triple checked my relationships, all the tables are present and accounted for. I don't see any table missing.

i'm trying to edit existing databases that connects songs with what songs are used in what tv show and for how long. i'm working with a couple databases. (1) list of SONGS with unique ids (SONGID) (2) invoices--each invoice with a unique ID details what songs (SONGID) were used for each show, and how long each song was used for (DURATION) i took up fmforum user, mr_vodka's suggestion (thank you mr_vodka), and have separate fields for each song entry in the invoice (no repeating fields). so each line item in the invoice is a SONGID and DURATION for the song. i thought that i would try to create another table that would create a new record ev…

Hello, I am trying to setup a fairly complex DB for data separation in FMP 8.5. I would like to ultimately end up with 2 files: - The data file which would contain only : *The data tables, with only data fields, keys and log fields (creation date etc...). *A table for user preferences and other user globals *A table for the user specified value lists - The GUI file which would contain everything else: layouts, graphics, scripts, non-user value lists, cascading delete structure, all calculations but NO user data. The obvious advantage being that I can distribute the solution and update it at anytime simply by replacing the GUI…

I have several to show the state of each clients account on there record. Usually this would be easy: (Contacts Tbl, Invoice Tbl, Payments Tbl) Contact Balance = Total Contact Invoices - Total Contact Payments But here is the wild card. (Bank Accounts Tbl) The Bank Accounts Table holds the information about each bank account each in different currencies. The Invoices and payments are in different currencies relating to the accounts. So, I need to show the balance of each Currency Bank Account on each contact record and i can not find a way for doing this without creating a seperate table with bank account records for each contact? C…

Hi - just joined, so a big "Hi" first of all. Only just discovered this forum and it looks great! I hope to be here for many years helping and being helped. I have a bit of a problem and need a nudge in the right direction. Any takers? My database is managing multiple "Places" at different geographical locations. I would like to be able to automatically produce information on the "as the crow flies" distance between the co-ordinates captured for each record of a Place. My "Place" table holds fields that capture "lat" and "long" aspects of geospherical co-ordinates and a calculated "distance" field that uses a custom function I created (well, the authors…

Hello, I have utilized some files posted in this and the portal forum to get close to what I need, but am having trouble with a final piece... I am using a join table to show items that a customer has selected. On the customers record, I also need to be able to filter those items by a category (portal). I am not quite sure how to set up this relationship. I feel like the attached file is pretty close, but I just can't seem to get over this final hump. Thanks for any help & advice, Robb Filtered_Join.fp7.zip

I'm really stuck on this question of reserving & querying availability of inventory. Say I have ten of a certain kind of chair in my inventory. They will be rented at various times, in various quantities in the future, and then returned to inventory. And then, of course, I have a warehouse full of all different kinds of furniture. I have got the inventory part, creating and adding multiples of the same item. I can relieve inventory (shipping) and return to inventory. But the part that has me stumped is creating a DB I can query that tells me whether that item will be available on any given day in the future. It seems there would have to b…

Hi - I'm tryng to figure out how to relate an inventory system to a reservation system. The inventory part I have - but how to be able to create a "reservation" - for a specific period of time - for a specific piece of inventory - is what is giving me fits. Any sugestions as to which direction to turn would be HUGELY appreciated. Thanks!

I'd like to create a history table for mailings sent to a group of records. Is there a simple way to create as a batch the new records in the history table? When I've done this in the past I export the id only to new records in the table, then fill the other fields for that group with the values required. Would the best thing be to write some kind of script? Ideally someone who understands filemaker will do this kind of thing, but I'd like the process to be somewhat robust. Thanks for any suggestions.

Any advice on how I can go about this: I have a customers db that contains a field called 'recommended_by'. This field stores a number that relates to a customer in the db, so when a new customer is entered I can select a number that corresponds to the customer that passed on my name from the list displayed. I have a record with the surname field set to 'Advert' so if a new customer calls from my advert, I select this as the recommended number. Is there a way of selecting a customer and then have displayed the path to that customer through recommendation up to 'Advert' name which will be at the top of the hierarchy?

HEy i'm fairly new to file maker but i thik i have picked it up fairly well, i have a portal on my screen and i have it linked to another page but i was just wondering if there was a way to make the table branch out eg (Risk Control measure= RCM, H= Hazard) Main Activity- H1- RCM1 for H1 ___________________RCM2 for H1 ___________________RCM3 for H1 _______________H2- RCM1 for H2 ____________________RCM2 for H2 They all vary in size depending on the hazard... and each activity has different amount of Hazards.... I can get it to show main activity down the side of it all the way same with the hazards but i just need it to be displayed once... or …

Is it possible to define such a thing as stacked tables in FileMaker? I have three tables in my application that have exactly the same fields defined for each of them. Each table has a fixed number of records so management of new records is not required. They were designed this way because they are accessed often and I wanted an absolute minimum of runtime overhead. In other words, I didn't want to define a Table::Type field with a join relationship and have FileMaker resolve the members of the set at runtime. I just wanted the tables to be there. As a simplification step, I would now like to define a BaseTable that has the necessary fields defined in it and…

I have a self-join relationship in a payroll table, which has the goal of trying to find the most recent salary entry that was greater than zero and from a prior date. The self-join relationship is as follows: ID = ID and Date <= Date and Salary > kcZero (a stored indexed calc) However, this results in absolutely horrible performance, about one second per record to update. Removing the two inequality relationships and it's very fast. Other testing seems to suggest that greater than / less than relationships are not fully indexed (even when all fields are indexed) and suffer a severe performance penalty. Anyone else seen t…

Hello everyone, I have a linking table with 3 fields (1 pk[row], and 2 fk[A___, B___]). How do i make the linking table with 2 parent tables, not allow duplicate foreign key rows in fmp? -- This is bad (because row 1 and 3 are duplicates) row A___ B___ 1_pk a1_fk b1_fk 2_pk a2_fk b1_fk 3_pk a1_fk b1_fk This is good (because the two foreign keys are never duplicated ) row A___ B___ 1_pk a1_fk b1_fk 2_pk a2_fk b1_fk 3_pk a1_fk b2_fk 4_pk a2_fk b2_fk Thanks for you help, -enki

Hello, I have two databases, Invoices and Mailing, each with one table. What we want to be able to do is link our invoices and mailing databases together. We figured the easiest way to do this is to link them through the full name field. So ideally, when someone buys something, we will enter their full name and their corresponding mailing information will pop up, assuming they are on our mailing list. This is all well and good, and I could join the databases in a heartbeat if it weren't for one problem - obviously there are many people who share the same full name. What I don't want to have happen is that I type in a full name that is shared by others, and …

Hello. I have a table named "Jobs". 2 of the fields in this table are named "Rough deadline" and "final deadline". What I would like to do is to display a list in the form of a job schedule alng the lines of: Job 1: Rough deadline: (date) Job 2: Rough deadline: (date) Job 2: Final deadline: (date) Job 1: Final deadline: (date) ...and so on. So the list is sorted by the date, rather than the job number. Is this even possible?

Not sure if this is possible... I have 2 tables "Object" and "Viewer". "Object" contains the fields: Type Height Width "Viewer" contains the fields: Type Smallest_Height Greatest_Height Smallest_Width Greatest_Width I am trying to figure out a relationship or calculation that will show the objects of a certain type and within the height/width range when entered into the viewer... but alas. So far i have failed anyone got any ideas ... file attached: best Stuart SizeRange.fp7.zip

i'm working with a couple databases. (1) list of songs with unique ids (2) cd clips--which contain a custom list of clips of songs. (hence, the song length is always varied.) on database 2, i have repeating fields for SONG ID entries, and SONG DURATION entries. what i want to do, but need help with: i want to see a song in database 1 and see how many CD's the song has appeared on. AND see the overall duration total. (e.g. if it has appeared on CD#1 for 10 seconds, and then CD#3 for 20 seconds, the overall duration total for that song should be 30 seconds.) how can i do this? thank you!!!!

Hi all I have this relationship-problem, which is hard to explain (being a newbie, like me , but here it comes: I have 4 tables "Person", "Company", "Post No" and "Booking" "Person" and "Company" are related to "Post No" to autoenter Post District. That works fine. Then I have "Booking" related to "Personal" through a matching ID. What I would like to set up is "Booking" related to "Company" through a matching ID as well. That means: Imported records to "Booking" are matched with IDs in either "Person" or "Company". (The IDs in "Person" and "Company" are uniqe) As its set up right now, its clearly wrong as the relationship between "Personal…

Here is what I need to accomplish... It seems like an easy script but perhaps my brain is just not functioning this morning... Two Tables, One called "Order Import Data" and "Shipping Info" Each record in "Order Import Data" has 5 fields of shipping information that needs to be extracted to the "Shipping Info" table. So the script would need to create the record and then using an order number relationship place the corresponding information into the shipping info table. It seems pretty straight forward but for the life of me, I cannot get it to work correctly. Any ideas would be a huge help. Thanks!

I have a bizarre problem that seems to affect some records only. this is a file I have inherited and am trying to clean up & reverse engineer. In the attached file (Guest account has full access) click on the "clients" tab and choose "Colour Chiefs" from the clients drop down menu. This record shows how this should work. You click on a contact person in the portal on the left and their details come up in the fields on the right. The other record "Test 1" does not work. It works by pasting a value into a field, but this second record will not allow this field to be pasted into. (See script "XYZ" – This simply does a "Set Field" which works on the first record but not o…

Hi here I have problem with relationships on job tracking data base. I start will creating an client and then setting up an Project. my problems happen when I wont to change my project client to another client it lose all the data in that protect, this only happen when I change the client iD on the project sheet not the client name. I have attached the data base which mostly like best place to start with this as it bit hard to explain thanks D BD_job_tracking_v5Clone.fp7.zip

Hi, I'm trying to find a way to create new tables with a relationship to my existing db using a script. The point is that I have a table "parts" and a lot of distributurs which are selling the parts. I want to link the new table f.e. called "distr1" to the table "parts". The user can should chose the related field in the "distr1" table which will be related to the primary field in the "parts" table. Any idea, of course a plug-in? Thanks a lot, Chris

I have got a data file (see attached) from a very old DOS system (someone told me that it is over 26 years old). I need to parse the information from this data file, and make it available for viewing purposes only in Filemaker. The data file will be updated every 30 mins. I am able to do this with scripting by searching for each number in the "f1" field, perform an export and then import into different tables. Lines numbered in "f1" as 321 are tied together as line items Line 1 = (field f8 is the account number- not unique in the data file) (Name, Address Phone etc.) (field f9 in line 2 also contains the account number). Lines 3 and 2 = fields f8 and…

Hi All, I just migrated a DB from 6 to 8. I have one problem. I can't delete a record from my Projects database because it says "one or more tables is missing." This is a multi-DB solution; after I migrated it from 6 to 8, I deleted some relationships, then the DBs that were part of those dead relationships. Can anyone help? Also, I must say that I don't appreciate the graphic display of the relationships. I much prefer the old way of view relationships. That graph can get very cluttered. Is there a way to view the relationships the old way? Without the graph? Thanks! J

I have 1 main contact database and 10 other databases that are all related to it. Certain emplyee/users do not have access to all 11 databases. For Example say I have database "a" the contacts databse, "b" is the appointment database & "c" is the payroll database. If a user has access to database a&b but not c they will sometimes get a promt asking for a user name & passwrd for database c. Is there any way to keep filemaker from offering a promt to a user with no access? Even if the databases are related? I am using server 7 & fmp standard 7. Thanks

Topic: Help with Simulating an Array Note: See attached "grid_example.pdf" (100K) I have included a pdf which presents a simple case of what I would like to accomplish. I have searched all about for a solution here but have come up empty. I need some fresh thinking here - an expert! I have two tables: Students, Registrations (i.e. class sign-up's) I would like to build a schedule grid in Students which shows the child-related records from Registrations in the appropriate blocks on the students layout (see PDF example). I know three ways to do this. 1) Single TO's for each "grid block" 2) A portal in Students and ten child-related records (f_…

What is the easiest way to put a portal in an unrelated layout. I have the main layout which contans a tab interface. The tabs are requests, Calls and Notes. How would I go about adding a portal on the main layout from the Notes layout which contains all my notes. When I add a portal on the main layout it grays out the pull records from. Any help is appreciated. Robert

I've got a problem with importing data and automatically establishing the correct relationships between tables. I'm working on a solution to quantify on a daily basis the number of railcars handled by our railroad for our various customers. I have built a table, with the usual customer data in it, including a field, which is the key field. I have also built a table, and it contains the data about each rail car handled. It also includes a field which contains matching data related to the field. Any given customer may have many cars. Unfortunately the format of the data I have to import every day (via FTP) is not user friendly in that it is old-fashioned, fix…

Not quite sure how to categorize this, but here goes. I have two separate databases (Files). One has a summary field that is put into a calculation to add it to another summary field and give a total. This works fine and displays the correct number. I also have another file that needs that data on a monthly basis. I have a relationship set up that uses a field in each database called cMonthYear (which is a calculation of MonthName ( date ) & " " & Year ( date ) . This also appears to work fine with one exception. The one field that is used in the first database gives me a correct result in the native file but 1 less in the related file. even if I drag an…

Hey everybody, Not sure if this is the right place, so feel free to move it where it belongs. We have a customer with a filemaker 8.5 solution that has a relationship to one of our databases to push data into our system. the script works.... some of the time What I have figured out is that when they open their system and FM tries to connect to our system, it prompts them for a username and a password. I added their usernames and passwords to our system and that solved the problem temporarily. Now what is happening is that they are changing their passwords to their system, and not letting me know. So when they get prompted for a username and password…

There is a table of film titles. The child table is all the roles of the people who worked on each film. When the user enters a role record in the portal, they choose a name from a popup list that is populated from a master list of all people who have previously been entered. There is also a master people table, with each person listed once. Question: how to enter a person's name in the join "role" table and have that new name also entered into the master people table? thanks for your help! I feel I must have done this before but can't recall how! Leah Brooks

Ok. I'm feeling pretty stupid here. A client of mine has a basic events solution that tracks event, venue, start date, end date. He was tired of looking at the events in list form and asked me to add a month view calendar. After some research I determined that John Sindelars' CC Calendar would be perfect for my clients needs. So I down load the free version but have yet to be able to get the events(appointments if you will) to appear on any of the calendar views. I followed the instructions "Swapping out A New Appointments Table: Free Version" yet still no luck. Anyone here tried integrating this product with their solutions? Chris

I'm probably overlooking something obvious, but I can't figure out why my relationship pulls in the data from the related table correctly unless the field I'm pulling in contains a mix of letters and numbers, even though all fields are "text" fields. In this case I just get a "?". The setup is simple. I have two tables with a simple many to one relationship (the second table has a list of values I use in a value list on the main table; hence many to one). I have two fields in the main table: Description and DX. The Description field uses a drop list for input, and the DX field is set up to auto-enter the related DX info from the related table when the Descrip…

Is there a way to develop an OR relationship? I have three fields in one database ProjectOwner ProjectRequestor ProjectDelegate I have one field in the other database user I want to display the record in a portal if user = ProjectOwner OR ProjectRequestor OR ProjectDelegate Ideas?

i am very new to this, using FileMaker Pro 6, on a Mac i have two documents, one which has all the details of every class that i am running, and one document that needs to be connected to it. when i select a class name on the second document, all the details should pop up, but i can't figure out how to make it work. help!

Hi, I'm having a small problem conceptualizing relations between three tables and the layout using these tables. A have those tables: invoices, clients and contacts. Here is my idea of the relationship. The invoice is sent to the client. A client is usually a financial institution, which can have many employees. Those employees are the contacts inside the client. A Client can have many invoices, but an invoice is for a single client. An invoice is ask by a single contact from our client. This contact can have many invoices to his name. My relationships are the following. Invoices: pkInvoiceID, fkClientID Clients: pkClientID Contacts…

I have a portal that show records (orders) related to a unique id field. The portal shows the ordre number, creating date and what has been done with the order. The portal can be many "records" long, what i need is it to show only the 2. last record fixed by date. Anyone who have any ideas?

I have a form that consistes of several fields from various tables. (3 fields from 10 different tables)If a record on the form is blank is it possible to enter the information on the form, and have it generate a serial number(in the original table)? And in doing this, will it check to make sure that the record entered doesn't already exsist in it's original table? (Basically I have several contact lists, and one form displaying the related contacts, they each have serial numbers (auto-generated when I entered them the first time))

What if you have multiple Tables that are all related and you want to use a Zip Code table as a resource for all? How do you accomplish this without creating multiple occurrences in the Table Base?

I have designed a report (called a Medical Service plan or MSP) layout that uses several portals to display data that falls with a specific range defined by the user. I created relationships between my MedSerPlan (the MSP table) and the various other tables and everything works fine. I have one portal where I want data on medications (both current and discontinued) displayed from the date a client enters our program through the end date of the search defined by the user. This portal worked on one MSP but then when I created a second one for a different client it did not work correctly (it displays nothing. This is how I defined the relationship between the MedSer…

Users want to select/de-select a Contact (while viewing the contact on the Contact's main form layout) and add them to a 'list'. They need to be able to name and date the list and select/retrieve those Contacts later. These lists will be things such as: 1) Send emails for new promo, 2) Duplicate an Order for everyone in the list etc. I do not want to flag the Contacts and I plan to write the ContactIDs to multi-line. However, I can't use global because the list needs to be there the next day (even if the databases are closed). Questions: 1) Field size: If I store ContactIDs as multiline in standard text field and the IDs are 10 characters, how many Contacts max…

I've created a relationship that doesn't work as I want it to.... (it doesn't work at all) Table 1 Table 2 Name User (text) = Name User (text) Text Season (global, text) = Text Season (text) Text Rel (global, text) = Text Rel(calc, text) Name Ski (text) = Name Ski (text) Layout 1 shows records from table 1 Layout 2 shows records from table 2 When the data in the fields in table 1 & 2 are excactly the same: Layout 1 shows no related records from table 2 in a portal Layout 2 shows related records from table 1 in a portal When the data in the fields in table 1 & 2 are different:…

Hi all, Question, I am creating a database that makes a User Preferences table (global variables used to store user settings while logged in, on logout, the script saves the settings to the users record or 'profile') Anyway, i have temporary global fields for searching, I want the user to be able to type in part of say the company name and part of the quote number, and see all quotes that contain those characters in the company name or quote number fields. This is possible? Hopefully someone understands what I'm trying to do. Thanks!

Hi I have created a drop down menu that displays the nae field for all records. I want to create a button beside this field that when pressed will take the use to the record of the name selected above. Any idea how to do this? Thanks Trevor

Hi everyone, I have a complex relationship which consists of a portal which relates to another table which uses Count() to another table. As User selects an item in this portal, they decrease the quantity which inserts an ID to the grandchild relationship. There is one instance in which the 'count' in the first portal will not update and that is when User changes their mind and reverses their request, ie, deletes the selection. When they reverse, the item is deleted but the calc in the original portal will not re-adjust the count back to the original. Neither Refresh Window [ flush cache join results ] nor Commit Record/Request will cause a refresh of that ca…

I am designing and devolping a solution... and need some help. I will have a table with multiple records and would like to count the related records. example: (records) #101, 36"x84" Wood #102, 36"x84" Wood #103, 36"x84" Wood #104, 72"x84" Wood #105, 72"x84" Wood #106, 80"x84" Wood would return a summary as follows 3 ea 36" x 84" Wood 2 ea 72" x 84" Wood 1 ea 80" x 84" Wood There are endless possibilities of differences. any thoughts?

Hi, I have setup a relationship between a field in between two tables. Records can be created or deleted in one table via the relationship. However, I would like to enable updating so if the data in the field in the main table is changed then the same value is used to update the related field in the other table. I didn't see an option to enable this automatically. Is such an option available or do you have to program the update yourself in some way (via a script?). Sorry if this is a dumb question I'm new to FileMaker Pro. Thanks for your time

I am making a special order table in my POS system and I want to be able to convert it to a real order if the customer ends up getting the items that they want. I am trying to relate the special order to the Line Items table which is connected to the inventory. Do I have to create an new table occurence to do this? It seems like I am missing something. I have never created new table occurrences, should it be avoided if possible?

Okay, I've searched through FMP8 help, searched online, searched here to no avail. I'm trying to build the HTML code for creating product "sets" on Cafepress. I have one DB to define the set, and another for the products which would comprise the sets. Right now, the sets I have each contain twelve products, but I'd like the flexibility to use other varieties. I could pull the calculated codes from each of the twelve products related to the set manually, but I'd rather concatonate the found related items into a calc field in the Sets DB. Maybe there is no way to do this, or maybe I'm just overlooking the obvious solution. Or maybe I'm not doing a good j…

Being a newbie, I always start databases with templates and work my way up. I started using a contact database for something I'm doing on the side, but then figured out that I needed to use the P.O. template to make a quick invoice for services rendered. Then of course I thought about all of the other things I needed to track. (I didn't plan it out.) Luckily, I don't have a lot of records in this database, so I can afford to start over with a plan, but I'm stuck on step one - the ERD. I have contacts and there are many contacts at different churches or venues. I perform observations at the different venues, but then I ran into one church that has 2 venues. …

I've read somewhere, thought it was here but I can't find it, where someone suggested that phone numbers have a related table to be able to pick type much like Address book in Mac. That way I can put in a number and tell the database what kind of number it is. Anybody know the best way to do it, or where I saw that?

Hello : I am trying to make a database for my friend. He needs an inventory, which is pretty simple. My problem is in the following: The inventory is for the computer parts. Most parts are interchangeable between different models and manufacturers. I need to make a portal that allows us to see all other parts that are compatable with the one that is currently selected, and to be able to select it in the portal and once clicked to bring it up in the main window. I am a beginner in Filemaker and still finding my way around and still bumping head, however i do have some experience with Access. If someone could please tell me how to accomplish what I explained…

Hello! Wow – the information provided by users on these forums is really impressive. I'm not new to FileMaker, but I am fairly new to using Relationships. I have 2 databases: DB #1 contains contact information, including billing address field, a shipping addresses, as well as a branch number field for some, but not all of my contacts. DB #2 I am using to produce a packing slip using the contact data from DB #1 for shipments. I currently have a Relationship setup between the 2 DB tables to separately Lookup the billing and/or shipping addresses for contacts that do not have a Branch Number; However, I was hoping that I could also use the Branch Number as a Lookup…

Hi all, This is a great forum and though I have lurked here often I have posted very little (if ever.. maybe years ago). I've built many databases over many years but although I'm not a novice, I'm not very accomplished either. I'm grappling with relationship post FM7 and figuring out all I can regarding tables and TOs but I'm not quite there yet with this problem. I think if I get to understand this I'll have made a quantum leap. I've attached an Excel Spreadsheet for clarity. The object is to manage a Share portfolio and report holdings and profits/Losses on the capital gain/loss. The first problem is multiple sales for the one buy. I can solve that but it's…

I hope I'm overlooking the easy solution... If I have a many-to-many relationship (my sample has articles and contributors), how do I set up a layout that allows for easy linking of the two? That is, I have a layout based on Articles that has a portal from Contributors, and I want to be able to create the relationships by clicking in the portal and choosing the contributors to the article. This doesn't seem to work across two hops in the relationship diagram. I click on the portal row and because the field is from a table not directly related to Articles, no record is created, and I can't display a drop-down list. I think I understand why, but surely there's some way…

(Originally posted in the wrong topic) Hi everyone! I am very new to participating on this forum, but have been a voyeur for a while, and have gotten a lot of good information, but I am still a beginner in FM) I am creating a database for a dating service. I currently have one table, it includes MemberID, MemberEmail and profile information (Most of which pulls from value lists). My question has two parts. 1. I need to keep track of the dates that the members are sent on. One person can have many dates with different people. If I enter date information (time, place, comments) for one person and add the member they dated, I would like the same da…

Hey Everyone, New to this forum and Filemaker Pro. I am running version 5 on a Mac and am trying to create an Employee time sheet for our office. There are 10 Employees - each must enter the number of hours spent on a project, what project, and what activity they did on the project. Each employee is to fill out a Filemaker form on the computer and then all 10 are to be hooked up to one master file which will allow the accountant to click print on specific reports. EG "Print all Projects John has worked on." "Print all Work done on Project X." Q. Is this the most efficient way to organise such a system? Q. How can I link all 10 databa…

Hi there My goal for this relation ship is the creation of a new record and seeing the newly created record in the portal, once it shows in the portal click and seeing the selected record. In my case once I create a new record it wont show up in the portal. Its hard to explain maybe just look at the file. Thanks for the help as always. TMAS Relation_Test.fp7.zip

Hello, can someone help? This is driving me crazy! I have a multi file database that has some relations. One file is a "contact" file with all the people, addresses, etc that has around 100 records. The other file, "Groups" needs to show 2 of the contacts in one record but each in a different tab. Example: Group 1 has moderators x & y, Group 2 could be moderators a & b, etc. If you click on Moderator 1's tab inside the group layout, it pulls up their contact info (looked up from contacts database) It works okay for one name, but the problem is that when I go to put in the other persons name, it only shows the looked up data from the first person'…

Hello, I'm new to using FM Pro 8.5, and have been working to try to grasp how to create a solution I need. I want to make sure I create this correctly so I don't start out on the wrong foot. I just can't seem to get my head around the relationship & table structure I should use. Here's what I'm looking to do: Analyze phone usage for individual users on a monthly basis. There would be multiple users with up to 2 phone numbers per person. The usage information will have a lot of different fields (I'll be importing this data from excel). I'd also like to identify the type of device they have. I've downloaded some samples on relationships, and I still …

I think I need to create a many-to-many relationship, but I am a little confused and not sure where to go for help. (Creating my first database, and it's turning out to be pretty complicated) I have a list of customers (numbered 1-121) and a list of managers assigned to the customers (roughly 70 managers numbered 1001 to 1070). Many of the customers have 1, 2 or 3 managers. Also many of the managers represent more than one customer. How can I display multiple records in the same table?

Hello all and welcome from a FM Virgin!!, I have just 'come across' Filemaker and currently running a trial version for evaluation and have a couple of questions. 1) When entering data into a field how can I make sure the actual data stored is in a certain format namely the first letter in each field is a capital lettter eg Name not nAME or name or NAME 2) I'm using a sample project to help me learn FM which is a basic membership system for a small gym. I'm having problems desiding how best to structure the data entry to allow the following (and of course the different pricing levels) Namely:- INDIVIDUAL (1 members) JOINT (2 members) …

HI I have a client (a child) who has parents and or carers and then they (years down the track) become parents to another client. I also have doctors, psychologits, liason staff etc. Do I represent all these people as 1 table (person) with a relationship table that relates client to person2, with the type of relationship (eg birth parent, carer etc), or should all these groups be separate tables?

Hello I am having a difficult time getting this to work, and I think it’s just me because in theory it’s very simple. A student can be in one or more programs. Each program requires payment. There are rules associated with payments (ie If all fees aren’t paid, then deny admission to that program). I think the relationships should go like this: (not 100% sure though) Student → Program Admission ← Program Type Student ←→ Payments Payment → Program Admission I want to see the students admissions to program “A” and payments for program “A”. But if that student is also admitted to program “ B” and has payments to “B”, I don’t want to see it o…

Hi all, In the past I have just used portals to create related records, but for this project that just won't work. I guess I would need to attach a script to a button to either switch to the related table or open a new window with the related table and create a new recored, but I don't have any idea how to automatically get the match field serial number from the parent into the new record. Thanks in advance for any and all help.

I have created a client database in FM6 where there can be several client contacts within the one company, all sharing the same accounts data. I want to make a one-stop entry layout in that one file, accessible in any of the several client contact records, that will enable me to enter/change/update accounts data once only and that will automatically update all the other records. I don’t want to create a separate file and create a relationship that way – I would prefer to keep it simple and all in one file. Have I explained it adequately? One idea I had was to choose a “Main” record among the several records, enter the Accts details in a special layout page and sel…

Hi: ¿Which is better, a solution with 20 related files, or a file with 20 related tables? I'd appreciate an advice on it. Tks.

This is an example of my problem. I have 3 fields: Vehicles, make and reg Plate... When I choose Vehicle and then Make, why doesn't the reg.Plate field get the right information? I want the reg field to get filled automatically. What am I doing wrong? Please help me...it has been bothering me for a long time now! I would appreciate any kind of help. Thank you ploblem.zip

I am having some difficulty with a new db that I am creating. I have created a relationship in the new DB with an existing DB. The problem that I am having is that the existing DB has two fields that I am trying to relate to 1 in the new DB. First, is it possible? Second, if it is, How do I go about it? Thanks.

Hello everybody! I am writing from Italy and this is my first post. I was trying to create my first invoicing solution.. this is the problem: i have created a many to many relationship (red table occurrences in the relationship graph) because to every customers are associated many items,and the price of these products is specific (=can be different) for each customer. This works fine; the join PRICE table is populated with a script associated to a button. in the INVOICE table, it should be placed a portal to create records into the related INVOICE LINES table. The fact is that I do not not how to relate the INVOICE and INVOICE LINES occurrence …

I'm new around here, and starting to learn FP7. I work for a small production company and have been put in charge up making some updates our system. So, here's what I'm trying to do. We're making a "Bid Estimate" portal and "Time Log" portal. In the bid estimate the employee will enter the different work the client will be billed for along with an estimated amount of time that will be billed at a variable rate. For instance: Bid Estimate: [Post Production] 3 hour(s) 150$ per hour [Production] 5 hour(s) 75$ per hour [DVD Authoring] 2 hour(s) 25$ per hour The time log portal will give the employee a field where they can pick th…

I am attempting to utilizes a relationship that relates a field that is a calculation (the total of two related fields). However, the relationship does not seem to work. I'm guessing it will not work because the calc is not stored, nor able to be stored. Any ideas?

Good morning all. My first post here with very limited FM experience. apologies in advance... Here is what I am trying to do: I am trying to create a quote form for my users. The form has a radio button for product material (containing two options; paper or plastic). This is basically the key to the form and where my problems have started. What I want to have happen is pretty simple i think. When a user selects one of the two radio options, I would like the corresponding fields below (color, size, style, etc) to represent options that only apply to that material. As far as table setups go, I have setup a table called ProductMaterial that consists of t…

I am semi-new to FM8. I am trying to create a simple contact management database for our office. I created a "Company" table and a "Contact" table; I made them relational with the "CompanyID" fields in both tables. I would like the user to be able to press a button to insert a "New" contact (while in the "Company" table) in a new and separate window. Right now I have a portal on the "Company" table showing the "Contacts". If I go and type the "CompanyID" on the "Contact" layout it joins, but I need to get the number there by itself. I hope that makes sense. Right now they would have to click in the portal so the contact is related to the company...and then I have a go t…

I have created an insurance database for my company. The trouble I have is figuring how to move data between two layouts. When I have a customer that cancels or dies I would like to check a box that would move that persons data from one layout to another so that they no longer show up on the "active client" list. Is there such a script? Any help would be fantastic. Thanks, Jason

Im trying to prevent adding new records in a portal. I though I could do this by creating a privilege set and using the limit option, but to my horror this isn't available under the create option in privilege area. This is going to be the basis of my invoice file, once its printed, I dont want to be able to alter in any way (master fields as well as portal). Many Thanks all Jalz test_security.zip

I am troubled to have to admit that every now & then we find orphan records in the database. Probably about once a month (mumble maybe week mumble mumble), when a list is ran there will be a blank row with no name etc. I thought I had commit records and a little field validation to prevent this, but obviously it’s not enough. What are some basic steps that would prevent orphan records? I can provide a table structure diagram if that would be more helpful. Just let me know Thank you in advance for your advice.

This is my first post. Glad to be here. I saw a telephone log on another application on a friend's computer that I thought was very cool. I thought FileMaker should be able to do that. I wonder. The layout appeared as follows: The top half of the screen was a single typical database record, name, address, etc. The bottom half of the screen was a phone log with columns for a date and time stamp, remarks, references and so on. The net effect was that my friend could call up any name in the database and see a record of every phone conversation he had had with that person. It seems to me there must be an elegant solution to this in FileMaker. Any takers? Thank…

Hi all. When I create relationships by dragging between fields in the two tables that I want to relate, does it matter which table I start the drag from and which I end the drag from? In other words, for situations with multiple relationships, is there functionally any difference if I have to create a new table occurence for one table vs. the other table? If so, can someone please explain the difference? Thanks in advance! Ken

Hi, In FM6, I used to have a LINK field in every table defined as numeric field, autoenter value 1. This field allowed me to link every record in file 1 to every record in file 2. It was very useful. However, in FM7+ relationships have changes. Now, if I want to link all records in one table to all records in another table I have the option to select the cartesian operator (X) and just chose any field in table 1 and any field in table 2 (as far as I understand it, the fields itself are irrelevant as long as they are not global fields). Is this correct? Can I forget about the LINK field? Does the cartesian relationship actually ascts as a replacement for thi…

Hi, I have to make a decision regarding an issue that has been a problem in a pre-7 database. A project is made up of line items 1 and line items 2. The invoice is made up of line items 1. Line items 1 are related many to many with line items 2. Line items relate only if they belong to the same project (line items from project A are not related in any way with line items from project . The profit for a given project is the result of: line items 1 - line items 2 I have to show in a report line items 1 and line items 2, and calculate profits line by line. Currently, this line items are part of 2 different files. The report is generated in…

I am working with a client who has a db which was virtually flat - it did have one table relationship, but should have had quite a few. Overall skills here are based on a flat model and I would like to make it easy for them to embrace the relational model. At the moment I feel that building that bridge is quite challenging because they are used to the conveniences of a large flat file. Is there an article or summary anyone could recommend to help with this education process? Thank you

I have an ugly hunch that something is staring my in the face here? In a CRM system, which have a company table and a related table of contacts, but according to who is opening and viewing each company record, should his/her contact in the company be sorted up into to the first portal line, due to it's use in letters/quotes/invoices. This means that login defines the sortorder for each record, further more should a "swap" button be attached to each portal line, making it posible to switch another further down up to the significant position as reciever of posts. Since we're in a multiuser solution can't data just be swapped via $variables and is BTW relatioa…

Hi. In short i am designing a database to keep stock at a guitar shop. Each guitar, keyboard etc has it's own unique stock number created with it's category code (e.g Electric Guitar = GE) and a 4 digit number indicating it's position in the grander scheme of things. Basically i've created a table for each Category (to allow me to serialize by category) and i want to access all the data through one Layout. Now is there a way to access all 30 tables through one layout and if so how do i do so in the shortest possible amount of time. I attempted linking all the fields in the different tables together (as each category has identical fields within it) to no …

Good Morning everyone. New to the forum, sent here by JMO. He mentioned that this site is a great tool to assist in basic and difficult questions related to FMP. I developed one or two databases, but missed the whole concept of Relational database (didn't have enough coffee and skipped the chapter). Anyway, went back and began making an ERD and came up with a problem I'm have trouble finding the right relationships. My DB concept is in a laboratory setting. Scientist need to order things from vendors, in turn I create Requisitions and submit them to the vendor. I want the users only to see certain things i.e. their orders, current orders (everyone's) an…

After lurking around getting excellent info I finally get to post! I have a Mac database of inventory items, unit cost, vendor, etc. Each "ItemID" is unique. I need to create a Kit listing layout for some ItemID's that are made up of several other ItemID's (components of the kit), a manually entered quantity and also display the component ItemID cost. I seem to think I need a self join relationship but I cannot figure out how to get the portal to work the way I want. In the portal I want to enter the several ItemID's I need to make the kit and the quantity needed and have the portal display this in addition to the component ItemID's cost, units & vendor fro…

Hi - I have three related questions, related to joins. Here's my situation - I have 3 tables: one tracks doctor's visits, then there are two related tables - one for patients and one for the services that occur at each visit. I would like to be able to keep track of how many times each patient has had individual services. In other words, how many visits occur with a given combination of patient and service. This seems pretty simple in concept, but I couldn't find a way to easily determine this except with a script that goes through every combination, does a find, and records the number of records in the found set. So - my first question is - is that the …

Hi all this is my first post. can anyone suggest how to solve a problem I have two related tables. The first has a data in which one of the fields is a category. The second has a global which can be altered to permitt viewing only data of that category. My question is, how can I set this up to electively see ALL the categories

I am having a mental block or maybe I just don't know how to do this... I made a simple contact management database and included it for yout parusal. This DB is just to help me understand how to do this. I have a contact record (will be used to record anonymous crimestopper tips). In another table, I store names. In the last table I store addresses. My problem is that I need to link names and/or addresses to the contact record. The same name and/or addresses will likely be linked to multiple contact records over time. In addition, there could be several names or addresses that need to be linked to the contact record. I am unsure how to make th…

Hi there. I used to work at another company that had FM - when entering a new record and the person you were entering had an existing record with the same name, a dialogue box would pop up and say that a record with that name currently existed and asked if you wanted to add the new record or edit the existing one. Does anyone know how that is done? Thanks!

I have a table with these 2 fields, both with Number results: cn__ilnMonth = Month ( cd__ilnDate ) cn__ilnPeriod = If ( cn__ilnMonth < 7 ; 1 ; 2 ) I am getting the correct results in the ct__ilnPeriod field. I am trying to relate this table to another by using this field and relating it to a simple numeric data entry field in the other table. It looks like something is must be wrong with the results of the Period calculation. I don't see any hidden spaces, so I am not sure what I am missing. Any ideas? Originally, I wanted the results of the calc to be "January-June" and "July-December." I have simplified it to 1 and 2 to figure out the problem, but g…

Here's my problem, I've been at it for weeks but still no solution. I have 2 tables, one for songs and one for songwriters. One song can have many songwriters and one songwriter maight have many songs (many to many relationship, right). How do I make this work, do i need a join table? and what would that be? I'd like to be able to report by songs: song1 has: writer 1 writer 2 song2 has: writer 1 writer 3 and also report the following: writer 1 has: song 1 song2 writer 2 has: song 2 song 3 Any help is good. Thanks a million.

Hey everyone! Had a question I was hoping someone could help me with. I have a database that holds person info and their appointment dates & times. I am trying to create a relation that will allow me to see who has a appointment within the area of an hour both before and after. So I created a Self-Joining relation with the date as one join, and the time from one to a time calc range, i.e. 3:00...5:00. This isn't working because the calc is a text, and not a value. Does anyone have any ideas? Much Appreciated.

This has been on my mind for some time, I hope i put this into the correct forum, please move into appropriate section for this topic. I am rebuilding the database (currently planning stage) for my entire company. There are lots of places to set user settings and such that could help out. Example: There is a table with a whole list of procedures on how to do various tasks at the company (how to process an order, how to open a support ticket, etc) I want to relate the procedure to the user based on the user's current layout name (names will be properly formated to include what department the layout relates to). How can i set up a relationship so that scripts…

I am trying to set up my first FM database to help with medical recordkeeping. I have 6 tables: CAMR- main page to find existing patients and add new patients on name, dob and date of exam. HPI- Contains fields for history of present illness PMSH- Fields for past medical and surgical history ROS-Fields for what is called review of systems Exam-Fields for exam I/P- Fields for the doctor's impression and plan. I have worked hard to create a layout for each of the 6 tables. However, I cannot figure out how to set up the necessary relationships so that as I progress from CAMR to HPI to PMSH to ROS to Exam to I/P that the appropriate patient's name, DOB and…

Hi All Just receive my 8.5 advance last night and I have been reading on the Forum lately about letting FM auto enter the serial number that the relationship is base on between tables. I am trying to understand how would a unknown serial number benefit you in finding information once enter? I will be redoing my Welding Program from Version 6 to Version 8.5 and would like to understand this concept. At the moment I use a calculation field to create a unique number to base the relationship on. Example (I use the following to create the unique field: Welder clock number Date tested Size pipe tested on Thickness on pipe WPS number these five fields creates a…

I have CALENDAR and ADDRESSBOOK tables related by CALENDAR::Tech[2] <==> ADDRESSBOOK::Name. Calendar::Tech[2] is a repeating field with two repetitions. I have a button that takes you to the ADDRESSBOOK related record of the Tech person....I can get it to do it for Tech[1] but can't think of how to do it for Tech[2]. Only idea I have is a run-around by having the script swap the Techs around, getting the related record from repetition 1, and swapping them back. But there's got to be an easier way! Thoughts? Thanks!

Any help greatly appreciated. Two tables: data and results. Trying to create a relationship where [data]name=[results]name and [data]date=[results]empty where empty is a date field with nothing entered. I'm thinking that find related records will show me all of the records in data where there is no date entered, but no. I've got to be missing something really simple, its driving me nuts.

Very similar sounding to Irenem (Post#235712), but I couldn't quite understand the solution, which is probably why I can't seem to work this one out for myself. I have three tables: Gymnasts, Classes and the child table Enrolments used to solve the many to many relationship between Gymnasts and Classes. There are four possible types of Enrolments: Enrol, Waitlist, Drop and Makeup. In the Classes file I have 4 portals, one to show each type of enrolment. After a lot of trial and error I managed to get that working. In the Gymnasts File I also have 4 portals, one to show each type of enrolment. After a lot of trial and error I still can't get this one to work. …