Relationships

Hi Everyone, I have 2 tables. One is People and the other is Correspondence. The relationship is: PersonID = PersonID AND Filter_Out_After_This_Date >= Correspondence_Date And Filter_Out_Before_This_Date <= Correspondence_Date I have a portal using this relationship within the People table. When i use this portal to create a new Correspondence record, i run into the problem that the date is set to whatever the Filter_Out_Before_This_Date field is, even if the first thing that i type is in the Correspondence_Date. When that happens, i'd enter something like 28/1/09 into the Correspondence::Correspondence_Date filed, press enter, but the newly cr…

Hi, I've got three tables, Company, Person & Job. Each company can have many people, and many jobs. Each person can have only one company, but many jobs. Each job can only have one company and / or one person. (Excuse the set up to this question) I can (in Company) find all jobs that company's had via the relationship of Company::IDCompany = Job::IDCompany I can (in Company) find all jobs for people who are still working at the company (necessary because a person might have left but not have a new company to be attached to): Company::IDCompany=Person::IDCompany AND Company::GlobalFieldSetToYes<>Person::Yes_If_They_Have_Left_Client …

Hi, I'm creating a database in SQL Server since I cannot implement FM in my customer business. It's been too long since the last time I went in SQL. How do we create a lookup for a field? Thanx

I just started tinkering with a free version of filemaker 10 on a mac. I'm hoping to use it as the primary tool to house all the guest information for my wedding I am going to begin planning. The standard contact info would need to be in there, as well as any RSVP's, etc. Is there a simple way to program such a database, find a template on the web, or buy one? Thanks!

Hi All Maybe I'm high or something here, but I'm stupidly missing something important about many to many relationships, and I need someone to explain it to me. : I get the concept of a many to many relationship - I think I'm just having problems implementing it in Filemaker. I've got two tables. There's a primary key in each table, both set as an auto-calculated serial number as a record ID. There is also a join key in each table which is supposed to link them together. The primary key field can't be edited, but the join field can (I of course, have to enter a number in there to join the records.) If the numbers match, then the records are related. I can get …

Hello, I'm trying to figure out how to make two fields relate to each other. I have one field called "ad size" which is just text (e.g. "full page") and another field called "inches" where we choose the number of inches (numerically) that go with the field "ad size". I would like to make the two fields talk to each other, so it is more mistake-proof. I apologize that I am a total beginner, but I can't find anything that answers such a simple question on here.

Hello, I have a question regarding images in the layout. This request stems from my limited experience making websites, so I'm not sure if this is possible within FileMaker. I'm using Ver. 8 on a Mac. I'm making an inventory for my friend's guitar collection. He would like to be able to add up to 5 photos for each guitar in the "form" view. What I was hoping to do was have one main square window for an image, with 5 smaller squares underneath. As you click on the smaller squares, the corresponding picture appears in the main square. This is common on websites like Best Buy where they want to give multiple views of a product. You click on the individu…

Hello! I had set up a Table with multiple fields in a seperate DB(aka: DB1). 2 of those fields were NAME and COST. In my current DB(aka: DB2), I setup a relationship for NAME (DB1::Name to DB2::ItemName) ItemName has 15 repetitions (I don't plan on having more than 15 records for now), and I want all my records in DB1 to display in the ItemName field. I can't seem to figure out how to do this... any ideas? thanks!

so i was looking at my relationships graph and on one of the relationships it seems that it doesnt connect to the graph and instead it floats next to it with and end that looks like this ( | ) i dont remember ever seeing this before could somebody tell me what it means?

Seperately, one link at a time, each thing I'm trying to achieve works. But I'm hitting a brick wall when doing both. I have two tables. Clients / Call Log. I show a protal from Call log on a layout from clients. My portal show limited but vital information regarding past call logs. In the same layout I have linked fields from the the call log table. A script that creates a new call log and captures the call log record id and the linked fields are now active. Each aspect by itself works fine. But when I create the relationship for both it limits the portal to that new call log record, which makes perfect sence to me. I'm either going to have…

I need to create a layout that shows the total price for # of feet of dust control applied. When a customer orders, their address record includes their county. The price by foot in each county is different. I'm trying to build an order/receipt page that shows: # of feet x rate = total I'd like to have that layout pull the county and automatically populate the rate for that county. Then I only need to put in the feet purchased and will have a calculated total. Does that make sense? If so, how do I achieve it? I know it has to be relatively simple but so far I'm not getting there... Thanks in advance. Sara

I have a contacts database with 400,000 records, and all the contacts live in the same general area. the address is composed from many fields and house # and street name are separate fields. It would be helpful to me if there were a field that showed how many of the other contacts lived on the same street, such that, if 2,806 people lived on wilshire blvd, the field would equal 2806 in all records where the contact lived on wilshire blvd. I'm not really sure how to do this. i'm guessing it involves a self join.

I've never really understood the new architecture of FM since V7 (being a veteran of earlier versions) so I just found out the hard way after deleting what I guess is the core relationship. All the records are now missing and in layout mode all the fields display The file still displays the table in the Table|Fields|Relationships setup window. How do I reconnect the fields and get the records back again? I tried just creating a new relationship to the table but all I got were those ::fieldname things. I gather there are some detailed instructions on how to do this somewhere? I'm also just happy to extract the content directly from the table - it…

I have a need to identify the largest numerical value in a single field among a set of records related to a single person, and don't quite know the best way to go about it. If you can help, it would be much appreciated. Here's what I have: A database "students" with all the students at our school (only one record per student) A related database "scores" with test scores for those students over a number of years. Each record in this db represents a single testing session, with scores in three different areas (reading, writing, math) Students who pass all three areas the first time through are done; they have only one record in "scores" related to their record…

I'm pulling my hair, people. It's like this: I have a table with line items, and some of them have to be linked with none, or one, or many of their fellow line items (basically, they are bank transactions, and they either stand on their own, or they have to be linked with one or many other transactions, because they relate to the same business move). Until now, I've had a text field. If a transaction stands on its own, the field is blank. If it's to be linked to others, then I go into that text field, and I manually (yikes!) enter the primary IDs of its related transactions. So, it's time to smarten this beast up. Any idea as to how? I'm thinking, setup a n…

I have created a relationship, so that a portal shows records where Company are the same, and Date Despatched are the same. When I view the portal the relationship works, when showing the records where company and date despatched are the same. However, when the Company are the same and the Date despatched are both empty it does not show these records, it only works when date despatched has a valid matching date. Is there a way to make it show records when both Date Despatched are the same but empty?

I am trying to filter clients in a pop-up located in a portal drawing records from a people table that contains both clients and vendors. The desired results is to eventually have two portals, each dedicated to clients and vendors in my projects table. I started with the clients portal that worked okay without the client/vendor distinction then tried to modify it so that the pop-up only showed clients. So far I have failed to create the distinction. I used in the Role join file a global constant with text result that says "client" and made the join to read: locate persons using the person key AND the global constant, the person's type field populated with either Clie…

We have been having a problem where relationships using < or > in them slow down creating new records and in some cases replace statements. Does anyone have any advice?

Hi there.. I am trying to do something but have no idea if it can be done LOL. Is it possible to calculate the relationship between 2 tables? [color:red]table1: mainrecords, products, product_ID and the targetfield where the path to an image should come. [color:blue]table2: images with product_ID, pathfield AND "imagelabel"( "img_main, img_back" etc). There is a relationship between those tables based on product_ID. Now I want to add another rule to this where the name of a field in table 1 corresponds with the content of the "image-label" field in table 2. Something like this: the field "[color:red]img_main" of record "1234" in table1 = [color:blue]image…

I have attached an Excel spreadsheet that should clearly show the type of information I want to have records of. I am trying to figure out if it should be one large "equipment" table, or if I should create separate tables for the different types of equipment since the fields are so different from one type of equipment to the next. I have been looking for an answer all over the internet for over 3 weeks and can not get a definitive answer. I will use this equipment record data to assign a vendor that has that type of equipment. More than one vendor may have the same equipment record. Can anybody help me with this? My biggest problem is that many of the …

Dear All, FMPA 9, Win XP sp3. I am just beginning a Bible database and was wondering if I am on the right track here. I was wondering if this requires the use of Recursive Relationships or can be done using our good ol regular relationships. Basically I have 3 tables- Books,Chapters, Verses. Minimum structure is key field in each table -say BookID in Books, chapter ID in chapters etc. Relate BookID to chapters with a foreign key field called BookChapterID. Book 1 will have say 1-5 chapters. Book 2 will also have 1-4 chapters etc(all different of course) It is the multiple instances of chapterID 1 in the chapter table that is worrying me. Ev…

Hi, How would you model the structure of the tables to know what was the price of a product in time? In a billing system it's important to keep the price up to date and also keep the price paid for a product for a specific time. This is one of my approach products (pkProductID, price (lookup from prices), ...) prices (pkPriceID, fkProductIDprice, date, ...) billDetail (fkProductID, fkBillID, price (lookup from product), ...) Thanx for any other suggestion.

Hi, I'm having a dilemma on designing a database. I would like to model an OO specialization. There are multiple solutions in database theory to model this. Check here Which one is best suited for FM? I should upgrade to FM10 by the end of the month. Thank for all suggestion.

I have a series of related databases: Trips, Parties, Clients. A trip contains parties, which contains clients. From the perspective of Trips I want the number of total clients in that trip. I have two portals set up in my layout. One shows the parties related to a trip. When I click on a button in a portal row of the parties db, then the clients in that party are displayed in a second portal. When I click on a button in the portal row of the clients db, then additional detail about that client is displayed in fields next to the portal. This all swell and is working perfectly. But... I have a calculated field in the Trips db that counts the clie…

I what to bild a date base of my workers what days they work . i am looking for ש idea how to billed it . can any body help me.

I am a long-time developer and I am experiencing a problem I have never run across before (two actually, but my other problem will have to be posted somewhere else). For this problem, I have a company database with a company id, I have a portal in the company database that uses the relation idCompany to idCompany (in the content database). So if a piece of content has the company id, it shows in the portal - perfect. If the content has a list of company ids, each separated by a carriage return, it *still* shows up in the portal (which is still great) - but that doesn't make send, because it is no longer equal. Now I have some records, just randomly, that also have a l…

I have two relational tables (Donors and Contributions) which are related in a parent-child (one-to-many) way. Over time, some Donor records have disappeared, leaving orphaned Contributions. In addition, there are some Donors who have never made Contributions. The key field is Donor_ID. I want to create a report that lists ONLY the Donors who have contributed, and their Contributions. The best way I know of to do this is to create a layout based on the Contributions table, with a leading sub-summary part triggered by Donor_ID, showing Donor information. In order to get the intersection between Donors and Contributions, I create a new window, and go…

Just for fun, I'm researching my family and putting everyone into Filemaker. Each person has a 'Father' and a 'Mother' field where I input the IDs of the parents. I have a 'siblings' relationship where other individuals sharing the same Father and Mother are displayed in a portal. I would like to show an individual's children in a portal too. But I want a relationship where 'FatherID = ID' OR 'MotherID = ID' and that doesn't seem to be an option. I know I'm probably thinking of this in the wrong way, can anyone put me straight?

I have three tables I am working with. Budget (which holds Week and Year), Contract and Income. I have my Income table linked to the Budget table with a multiple match criteria of: Week = Week AWO Sold Year = Year AWO Sold This is working perfectly and I am getting the calculation in the Budget table that I am looking for. But, I tried the same multiple match with the Contract and Budget table and am having issues. Week = Week Contract Installed Year = Year Contract Installed It does not bring up anything in my budget table. If I remove the second criteria it will calculate based on the week, but when I add the year in, it does nothing. I…

Hello, I have a relationship problem, I have been playing around with it but just can´t get it to work. I have 3 tables contacts, clients and jobs. As a client can have more than one employee, I store the employee details (name, telephone numbers) in the contacts table and have a relationship set up through an id number. This info is viewed through a portal on the client table. The client table also hold the company details (name, address), this all works fine. What I am trying to do is on the jobs table, I have a drop down list, which is a value list holding the id number and company name, when you select the company name it automatically fills in the address fr…

I am trying to create a tool for print production that will identify scheduling issues to our Marketing Managers. They will be required to fill in a field for "Availability Date" I would like then to create a field that will back out that date and either have a warning pop up or have a date displayed with a warning color (either the text or the box or both) that will tell them that their Date is not possible given it will take x amount of days to actually create and approve thier project. The dates that a project will take will depend on the type of project (I have 3 levels: New, re-format, and reprint). Is this possible to have this functionality in a field?

Hello All, I converted an FM5 multi file FM database to FM Advanced 10.0 ... Found what I needed... Thanks, Nancy

Hi , I'm a newbie to 9. I have attached a file to better describe my question. I have two tables: Invoices and Attachments. Each invoice can have may attachments but one attachment can only be linked to one invoice. Each attachment can have a status of either Active or Inactive. At any point in time, an invoice may have both active and inactive attachments. I want to show the Active attachments on one TAB and the inactive attachments on a separate tab related to each invoice. How could I do this? attachments.zip

Hi all I am trying to upgrade how welders continuity log file by using a portal instead of a list view layout but I came across a problem on what field to base the relationship. Here a little history on how the it works. A users press a buttom and a serach range of dates are created automatically bu using the Get(Currentdate) and the Get(currentdate)+30. This creates the date rnage to search. Then the welders that needs to be requlification in between this dates are displayed in a list view layout. I would like to create a portal to perform this function but I am at a lost on how to create the relationship to displayed the records because the records do not have any t…

I'm trying to setup this for a small business. I am creating a table to hold contacts informations. The fields are name, address, city, phone, etc. My problem is that I can have a customer to whom I will be providing services on locations different than the billing address. My invoices will have to have both the billing address and service address. If they are the same, I will click on an arrow to paste the billing address to the service address area on the invoice. However, if they are not the same, or one of my customers have more than 2, 10 or more locations, how do I create a button or something I can use to add new location? Am I making any sense? I'…

Not a database guru which I will make obvious in a moment... Basic question: Can I create a group of records (8-10) through a portal that will have unique data auto-entered in each field (differing from one record to another)? I'll try to keep this as short as I can but still give enough detail for clarity. The desired function is to keep records of the music that is used each week in the church services that I plan. I current have three primary separate files: 1) Services - individual records for each Sunday; includes fields for calendar date, liturgical day, and others; 2) Master Song List - one record for each song title with various fields for composer, et…

Ok, so every article I read that talks about Order Entry Systems and how Invoices, Products, and Invoice Items relate, all seem to rely on a fairly simple products table. In each example, a widget, is a widget and a whatzit is a whatzit. The system I am starting to lay the ground work for is a tad different. For example, say I have 1000 of art files. And those art files can be produced on 10 different items. To fit into all of the examples I have seen, I would need to have a product table consisting of 10,000 products. But, what I would really want is a table of art, and a table of items, and perhaps a table that joins the two together so Art001 …

We have a fairly complex application with dozens of related tables in one particular database. We build complex html files and URLs from these related tables. I'm not a professional developer however over this thing has got big and complex over many years. When building HTML & URL's we need to be able to use characters that are normally reserved for special uses in Filemaker calculations, eg speachmarks are reserved for identifying text in Filemaker calculations. The way we get around this is to have a Constants Table in the database. It only has One Record, and in the various fields of that record we store all the illegal/special/reserved characters such as…

My problem is this: I want to be able to search for a record and have the other related fields auto fill in based upon the record searched for. I have a database of taxonomic names that goes in this order (commas represent new fields): [color:blue]ORDER, SUBORDER, INFRAORDER, SUPERFAMILY, FAMILY, SUBFAMILY, TRIBE, SUBTRIBE A typical entry looks like this: [color:blue]Coleoptera, Polyphaga, Cucujiformia, Chrysomeloidea, Cerambycidae, Cerambycinae, Trachyderini,Trachyderina I want to be able to enter "Cerambycidae" for FAMILY and have the other fields that come before it fill in (order, suborder, infraorder, and superfamily). There are thousands of…

I am new to filemaker and am currently using FM7. I am trying to create a system for tracking clients, appraisals and items. I have 3 tables one is CLIENT (client_id[serial, auto-enter], First_Name, Last_Name, Address, City, State, Zip, Phone, Email) APPRAISAL (appraisal_id[serial, auto-enter], Appraisal_Purpose, Appraisal_method, client_id) ITEMS (item_id[serial, auto-enter, Artist, Height, Width, appraisal_id, etc,) I have created relationships between CLIENT:client_id & APPRAISAL:client_id APPRAISAL:appraisal_id & ITEMS:appraisal_id I then created a button on CLIENT that goes to APPRAISAL and Creates a new record. The issue i…

Hi Everyone I am having no end of trouble getting a lookup/autofill to work as I want it to. I have got it to work in various parts of my program, but not this one. I have included an attachment in excel showing 3 columns. The first 2 columns work fine, its the 3rd column that will not work. Further down the spreadsheet is a picture of the screen layout. Any help would be greatly appreciated. Milton I tried to attach but couldn't find any way to????

I have 3 tables in my PR database: media information (key=MediaName) journalists (key=Journalist) project (key=ProjectName) media information has multiple journalists in a portal in project I would like to (theoretically!) be able to choose 20-50 of the MediaName into a list, and have project related fieldsfor each MediaName. It should also be able to bring in information from Journalists. Could you recommend the correct, or smart way to set this up? I am really a beginner so apologies for such a noob question, but do appreciate any help.

I have a table of workers. I'm trying to make a table to track who worked. Multiple workers in a day. A Days table. I have setup the relation ship of WorkerID to each table but I'm not sure how to allow multiple reference's. The number of people working per day varies from 20 to 80... Don't know if that info might give you some idea. Each day would be a new record in that table. How do I create multiple relationship field's? I'm guessing I might need a calculated result. Regards, Mike.

I'm continuing to have problems when I seek to rename a table. Specifically, I create a table with a name like "test_table" during early testing. Then, after I rename the table using "Manage Database," it simply becomes completely invisible within FileMaker; when I try to create a new layout to reference the table, it does not appear in the list of available tables. This is very, very frustrating, especially since it has happened multiple times and every time the only solution is to start over from scratch, with a completely new database file, and manually re-key all the scripts, data, etc. I am sure that I'm missing something obvious, since I see many referenc…

I am trying to have a user friendly navigation of records within a layout of a table. The user would only need to click in the portal from a self-join relationship to go to the related record (i.e. sessionid). Now the portal is intended to show a filtered subset of records, according to criteria from a childtable (i.e. only future dates and empty dates in this example). See layout 'test'. So far I have tried a selfjoin based on a calculated field 'cwaitinglist' having a value 1 in case the criteria are met, and an auto-enter field 'c_one' (also tried a global storage without success). From the forum I learned that the calculated field can be used in a relationship…

Hi all, Hope everyone had a great new year. I am trying to do the following: user selects an item from a pop up list. Once this is done then the user is asked to select from a popup list for items only connected to the first choice. The tables are: 1st Table: Account type:, PK AccountTypeID, only other field AccountTypeName. 2nd Table: Account::, PK AccountID, AccountName, AccountNumber, Balance, BalanceDate, TaxType. Do I have to add fields into both tables so they can be used as lookups? and what should I link the two tables by? Thanks again. Milton.

Hello. Please forgive me if the solution to this is obvious. I have looked through many threads and have not come across anything exactly like my problem. I have an Inventory database that needs each bottle of (vintage) wine to have a unique entry (meaning, no quantity field because the quantity is always "1"). But when it comes time to invoice, using an Order database, many bottles and many varieties may be included in the portal. Right now, they are showing up as individual line items, related by the PO Number. The subtotal function Count is working, as is the subtotal of Sale price and appears like this: Qty Wine Each Amount 3 …

Hi All: I have a filemaker database of our customers. There are many duplicate companies but each has a separate contact. Each record also has a "CustomerSas" field which is a number field with a value the means something internally to our company. Is there a report I can run that will sort the duplicate companies and display the value in the "CustomerSas" field most often? Any help is appreciated. Gary

I have a system that logs radio frequencies. For each frequency (record) has 4 calculated fields I need to compare each frequency in the database all other frequencies and all calculations. The comparison would check ±20 to see if any of the other freqs (or calculations) are in range of each other. How best to achieve this? Many thanks

My db has a field that has YMM-XXX where Y = year number (I know, 1 digit can cause problems, but it's an inherited db and I'll get to this issue soon), MM=month number, and XXX represents the serial number of the entry for that month (we typically create 70-110 new entries each month). I want to have this be an automated entry that resets the XXX to 001 on the creation of the first record created on the first day of a new month. I've played around with different approaches, but none provide consistent results. Any ideas?

Is there a way that I can have a field to automatically format? i.e. have a phone # go from an entry of 10 digits to (xxx) xxx-xxxx.

Hi folks. I print letters which are medical reports about patients. Usually I need to send a copy to other related practitioners - GP, etc. I've set that up on a letter table - each letter has an ID, and then I create child records, one for each 'copy recipient'. Then I create a printed report, with the text of the letter appearing in the leading subsummary (sorted by letter ID), and the 'body' appearing at the bottom of the page (one or more lines of contacts under the heading "Copies to"). I print one copy for each 'copy to' plus one copy for the actual addressee. That's coming out fine - but the problem is when I get a letter that doesn't have any 'copies to…

I am importing a set of data into one table, and then running a script to "transmogrify" the data to be stored in a different table. My problem is that it seems that the ONLY way to actually do this is to create a separate temporary variable for every field in the source record, because once I change the Layout to store data into the new record, the source table is no longer in reference. Even though I've 'graphed' a relationship between the two tables using a unique index field, when I try to reference the "source table" while the layout for the "new table" is in scope, I get error messages. Am I missing something simple here? I'm actually starting fresh after a b…

Hi I have been given an excel file with all of the children records. I was wondering if there was an easy way to separate out the Parent records when I import the table containing the parent info. cheers

Seasons greetings, everybody! Please login to the Guest account in the attached file, then click the Enter button on the splash screen--you should end up on the Documentation tab. (Mind you, this is a work in progress so please excuse the digital dust.) I'm having a devil of a time trying to set up the TOs and relationships properly so hopefully one of the more wizened among you will know how to make the solution work: What I want to happen is when a user enters a document number--in this example, 3210-06--the rest of that document's related fields will autofill the portaled record with related data previously entered in the Documentation table. I created the A…

I have three related tables: Case, Part, and PartBilling There is a one-to-many relationship between Case and Part, likewise with Part and PartBilling In a layout showing records from Case, there is a portal showing the related PartBilling records. However, within the portal I also want to display data corresponding to the appropriate Part (parent data). This parent data does not display appropriately; instead each instance of PartBilling in the portal will only match data from the 1st Part record related to Case (even if that record is not related to PartBilling). What's going on here? It seems as if within portals FileMaker can correctly navigate …

HELP I accidentally deleted some relationships in my Relationships Graph. I manually added the table instances and redid the relationships. But I still can't see my data that was in the system before, nor can I use some of the scripts to access functions. DI I have to restore to a point in time before I made the deletion, or is there another way to fix what I broke? (I started looking through the data after I posted - I can see older data, just not new entries). For example, I click a button that launches an 'add note record' script. I enter the note, and then go back to look for it and it's not posted in the main record's portal where expected. IS…

I have developed a report-writing database for my school. I have one table with individual student details and one with teaching Unit overviews. For report writing I need to automatically populate a student field with data from the other table( or alternatively just display the related data field ) according to a specific code e.g. Student 1_WWE_code 1_WWE_description ( gets unit descriptiona ccording to code in previous field ) ... Unit unit_ID_code ( unique ) unit_description ( to be shown according to the code in 1_WWE_code, etc. ) In the relationship view I have set up a many-to-one relationship between the 1_WWE_code... fields in…

Making a database with shifts people work. I need the ability to have them span nights. so i have a date start and end field and a time start and end field. I made a calculation startcalc and endcalc that is a timestamp so i could have one field to reference for relations. I have a relationship to another occurrence to check for overleaping records like this ID != ID startcalc < endcalc endcalc > startcalc this was working and without being aware of why it now doesn't is there something funny with timestamp fields or something i am not getting here??

I am new at developing Filemaker although I have used it in the past for data entry. I have designed my tables / Fields everything was good except when I opened my filemaker database this morning it was showing " unrelated Table" in all of my fields. Please tell me what happened. or a few suggestions as to how I may fix this. I really do not want to redesign my layouts as they are quite extensive. Thanks in advance.

After several trys I think that I've gotten completely stuck with the idea what I'd like to have: I've two tables: menu and user. their content may but must not be connected by entries in table menu_user. For easy maintenance I'd like to have a layout menu_form that shows a menu, and all user(s) connected to it and all user(s) that are not. In both portals showing the related/not related user(s) I want to have a button allowing me to delete a certain menu_user-connection or to establish such a conection. My example has worked a bit - but not correct for all cases. So after two days of experimenting I've cleaned all scripts updating table menu_user and decid…

I'm trying to create a table, "type." From "type," I want to be able to add table upon table - i.e. one type would be "people," and "people" would then allow for "clients," "personnel," "vendors," then "personnel" might have "editor," "cameraman," "admin," and so forth. What is the cleanest way to create this branching structure and allow users to define additional types (without creating redundant ones?) Thanks!

I've managed to make it this far in my database, but I've run into an issue that my friends and I can't seem to figure out. Any time I create a new employee record, it automatically creates a new company with it. The idea was to have multiple contacts in one company that you could view in the employee tab. This would eliminate having to switch back and forth between windows. You'll see by the format of the attached zip file, that things should be fairly self explanatory. I'm not sure what it is that I'm doing wrong. Can anyone spot it and help me out? P.S. - Be sure to delete all the records first in both the company and employee table. Then try adding a…

hello, I am having trouble looking up values from other tables, in the ISSUE table, I want to select a the tech name field and have the field TECH NUMBER be populated, the data comes form the TECHS table, but I just can't get it to work, thank you ALLTECH.zip

I have a database that has a table with a list of techs and the contact info for them but also have a seperate table that has a list of all my clients. what i want to do is on the main page of the database that gives me an overview of the client, i have a portal that lists all the techs that are associated with the state that my client is in but i want to be able to have a field calculation that i can do that will show the distance between the location of the tech and the location of the client based on the city and state between each so that i can scroll throught the list and find the techs that are closest to the client and then view the info for the tech and call them …

Anyone have a suggestion for this: Have three main tables: Inventory (products) Invoices Customers The Invoices layout contains a portal that pulls from a join table between the invoice and the inventory to pull things like product name and price over. The Invoice has a direct relation to the customer database to pull the usual name, address, etc over. The Customer database has a portal for multiple lines for a quoted price for random products (Customer 1 might only have quotes for Product A, B and C, while Customer two might only have quotes for Produts B, C and F), that is also established by a join table. Now the issue I am having is o…

First off, I apologize if this is answered elsewhere on this forum, but I wasn't able to track it down. I've got two tables, one for "companies," one for "contacts." The contacts are individuals who may work with more than one company, so I've got a field for "companies" in the "contacts" table. I want a portal in the "companies" table to show all the contacts who work with the company whose record I'm viewing. So I need to create a relationship based on the "companies" field in "contacts" containing a company's name, but not necessarily being limited to it. Does that make any sense? I feel like I explained it really poorly. Thanks in advance for any help!

I have a table in my data file, which I would like to replicate to an identical table in my online file. The data file is the core foundation of the solution, while the online file serves web access (view only) The Travel table should be replicated from the data file to the online file, every time any record is modified or added. I know that I could do it with a zipp script plugin, but I have seen that this process can failure. Is there any smart way to do this ? (fm9 klients - fmsa 8.0.4) I have a robot setup, but it only run once every day and it would be nice with instant replication....

Hello there. I'm stuck and I hope somebody can help me out a bit, I've isolated my predicament in a zip file. To protect the innocent, all names are of baseball pitchers from the sixties. Here goes: Let's say I've got three Departments (offices, warehouse and workshop), each Department must create security Teams (fire, first aid and evacuation), each Team has several specific Functions (they're all in the database, have a looksee). I'd like to keep all Team members in one field, instead of splitting them all into separate fields for each Team, if at all possible. But since some employees belong to more than one team, repeating fields seem like a logical way to h…

Greetings. My query concerns working with repeating fields in relationships. Table 1 (Client) has the following non-repeating field: Company Name. Table 2(Projects) features two non-repeating fields: Project and Client. The match field between Table 1 and Table 2 is as follows: Projects (Client) = Client (Company Name). The goal of Table 1 is to add projects to the single client record (through a set field option in a portal on the Client layout) so that all of a client's projects can be managed from a single record. Table 3 (Timecards) features the following repeating fields: Project (repeats 10 times) and Client (also repeats 10 times). Table 3 tracks billable…

Hello All, I've got a database, lets call it Master which has a portal displaying contents from the child database.Its a one to many relationship, which works great. However I now have the need to filter the data that s displayed in a portal, using multi critera this should be easy. The problem is the data I need to related to from the Master file in the grandchild table, which is incidentally linked to child. Is there anyway I can filter portal records based on two tables without having to duplicate the field in the child table (my nice structure would be seriously messed up) I have posted a pic of a basic example of what I mean as I'm not very good with words…

Hi, This is my first post here and I have a problem with a relational data as shown in the img below. Written problem: I am trying to relate one price field from the inventory layout and relating into the sales sheet layout. now the only way i would know how to do this (wich doesnt work) would be ITEM NAME is Related from the layouts INVENTORY and SALES SHEET, and the item prices 1 2 3... and so on is Related :: so if ITEM NAME IS = INVENTORY AND SALES then RELATE SALES PRICE But that doesnt seem to work, i beleive that the price relations would have to be separate to work but i cant have them separet. So if anyone can help me with this i would be very g…

Hello, thanks for taking the time to read my question. I have an idea of what I want to do, but I'm mixed up about how to go about it. Basically, I have a "Contact" table and a "Companies" table. From the "Contact" table, I need to be able to A) Create new records in "Companies" : Select existing companies from a drop down From the "Companies" table, I need to be able to A) Create new records in "Contact" (First Name, Last Name, Phone Number, Email Address) Select existing contacts from a drop down list sorted by name I apologize for the disjointed explanation - as you can probably tell, I'm not really sure how to think think about this pr…

I decided to add an editable field to a portal that is used to choose 'assignees' from a contact list. This is accomplished through the use of a script. Everything has worked fine so far. My problem is that after I created the Assignment field and added it to the portal it does not allow me to edit the field when the new record is created. I tried a number of ways to get it to be editable, but nothing worked. I am not too knowledgable about scripts, so perhaps it has something to do with how I create the new record. I've attached the file, since trying to describe all of the t.o.s and the relationships will probably only be confusing. I am referring t…

Hi Everyone, I am wondering what is the best way to solve this problem... I have a table called Stocktake which has a portal linking to a table called stlineitems via a relationship with two fields representing month&year. However, the stock exists in several locations and I want to be able to report per location. So I have a field in stlineitems called "Depot" where you select whether the machine/stock is located at the main warehouse, or at any satellite depot. However, I then require some unstored calc fields in my main inventory table representing for example, how many machines of type X exist at a particular location - such as Scotland etc. …

Hi everyone, I am facing a bit of a dilema with a solution I am working on. I essentially have a set of jobs that need to be completed, and have used a formula in a script to work out the distance between a Starting point A and an end point B. It then works out the intermediate distance between Point A and the job - lets say point M - and stores that in a field. It does the same for the distance between point M and point B, and stores that in a seperate field. It then excludes records outside certain distance ranges, and sorts them by incrementing distance from point A. My problem is that between 2 and 3 users are constantly going to be using this solution …

I am the only person with admin priviledges on my database and have not made any changes to schema. All of a sudden I seem to have a broken relation ship. I have a Merchandise table linked to a shipments table. Shipments has an Auto-enter serial number in the field IDShip. I will place that value in a field (also IDShip) in Merchandise. One Shipment to many pieces of Merchandise. So, I can see if and when an item has been returned to my client by putting Shipments::DateShip in my list of merchandise. Now all of a sudden, rather than a date, I see If tried removing and replacing the link, creating another instace of the table and linking via that? No jo…

I've been racking my brains over this one and I am sure I am missing something simple. I have a drop down list that won't display anything but if I type in the ID the correct name shows up. Here are the details: Projects: Project ID Name Tasks: Project ID Task ID Projects Project ID (based on Projects ID) Project ID In Tasks I have a field displaying Projects Project ID::Name, using a drop-down list displaying values from Projects::Name When I click on the field it does not show anything. If I type in the Project ID, the name pops into the field. I've attached the file if anyone wants to take a look. Thanks. BPS_copy.fp7.zi…

Hi, I’m trying to set up a database which collects similar information, but I’m not sure about the best way to store the information. It is a training database which tracks Employee Info and the training they can have. Currently, an employee can have up to 40 different types of training, but for this initial purpose we will only identify 2 to keep it simple. So with simplicity on the mind here are some tables and fields: Employee_Info_Table: Field_EmployeeID Field_EmployeeName Safety_Training_Table: Field_EmployeeID Field_IsApplicable Field_FormalTraining Field_LicenceNo Field_CompetencyScore Field_ExpiryDate Field_TicketStatus Fi…

I need to create a portal showing the records that do not match criteria. I have a table showing appointments and one showing people. I was able to join an instance of the appointments table to itself to show any overlapping appointments. There is a portal that shows any people currently attending an appointment. I need one to do the opposite. I need it to show all available people. Any ideas would be great

Could you please help with relationship and restricting user access to only their pertaining task and role records I started developing the relationships but not sure if it is correct and not sure how to limit user access. Please see attach file and scenario below. thanks a million. A person can have many roles. A role can be given to many persons. A person can have many task. A task can be performed by many persons. If a user enters the db with account name John and password Doe then he can only view and create tasks pertaining to himself only. If a user enters the db with account name Maria and password Gonzalez then she can only view and create …

Hello and thank for reading. Problems with lending library 1) Does not allow a person to book more than 1 equipment e.g. A laptop + projector? Or does it? 2) Does not allow pre-booking like weeks ahead (Post#279923 by Fenton Jones in this forum resolves this, I think) 3) Does not allow you to cancel a booking. Background info: We are a small department of a university. We have some equipment that students and outsiders hire from us. Students are not charged but outsiders pay a fee (don’t need to record how much they pay etc as another system looks after this). The equipments are hired for Workshops, Seminars, Public Lectures, Conference, Student-Presentat…

Hello I am working on a two file solution that has many tables, the first file is cloned for customers every customer gets the same file wich is given a unique name, the second file is different for each customer. All are served by FM server 9. My problem is because the second file is different for each customer (and has to be to named differently) how can I deal with this without recreating all the relationships. The second file holds contract details ie. bank acc., National Insurance, Payments etc. I can explain myself a little better I hope, if I have more than one identical solution on the server, ie one for companyA one for companyB and each one has a …

Hope I explain this correctly: certain fields in a table/layout are not populated; ie I click on New PO while in a Jobs Table. That new PO in the PO table should have the project name, company name, etc. already in the record (about 5 conditional fields), but only 4 are populated. In previous versions of this file (it's commercial), this worked just fine, but now I have to select the company from a drop down menu. My question: where do I look to change the function of this field so it automatically filled?

Please forgive my inexperience with FileMaker. I have used it to make slight adjustments to the templates previously, but I am now trying to use it to build a custom library management system to replace InMagic. I searched the forums and I was unable to find anything that would help me, but maybe I am too new to even know what to look for. Regardless, let me speak to what I need and any help would be very much appreciated. Simple book management is easy enough with the Lending Library template included. The trouble comes in with journal management. I am attempting to create a system where I have a database of each titles entries as a unique database. For example…

Please see attached jpg for image I am beginner to FM and need an expert (or novice) to help me. Thanks! This list displays in a webpage This list is a portal from 'inventory' table. User enters qty of each list item but the 'qty' field should reside in 'order' table. how do I get the qty fields in 'orders' to reside with this list created in 'inventory' So if user enters '3' of line item one, '6' of line item 4, how do i create a relationship so that my 'order' record will contain the qty's the user has entered based on the line items within 'inventory' table? Currently, the inventory table list you see is a self related global. What type …

I have a table of all the different shipping options my company provides and the amounts we charge for those methods. Here is my issue. A portion of our sales invoices are downloaded each day from Paypal. The comma-delimited file that Paypal provides lists only the shipping amount charged for the method selected by the customer. There is no option to see the actual name of the method selected. As a result when downloading these sales Filemaker must look at the shipping charge that is downloaded and correlate that amount with the correct shipping method from my shipping_methods table. It's a backwards way of doing it, I know, but with Paypal there is no other option. …

Hello! Another fairly new Filemaker user. Im trying to make a database that shows which items users have. Im currently using two tables. Table user has fields; username item1 item1type item1size item2 item2type item3size item4 item4type item4size etc... Table items has fields; serialnumber type size I have tried to use lookup for item fields, but once I enter item serialnumber to item1 field => item1type and item1size gets value from table items, but so does item2, item3 and item4 (and the value is same in all fields)... and I cannot enter multiple serial numbers. What am I missing here? How can I ma…

I have a numeric field in a Data table that normally contains a value. Since FileMaker can accept alpha characters in such a field, the field allows users to enter an error code when a value isn't available. That much is fine for most processing, but I've run upon a problem with portals. I have a relationship defined between a text field in a Master table and the numeric field in the Data table. This relationship is solely for the purpose of allowing portals to exclude records that have a particular alpha code entered (for example, "E") when no value has been entered. Regardless of whether I define the field in the Master to be numeric or text, the portal alw…

I have a DB solution so workers can sign up for work, but select the Activity, Date & Time to sign up. Tables [fields] Activity [ActID] Activity Date [ActID, ActDID] Workers [WID] Signup [WID, ActID, ActDID] Each Activity can have many Dates/Times I would like a worker (from the Worker table) to select an Activity and Activity Date from a portal to sign up. Problem is, I can't figure out how to get the ActID and ActDID from the one portal to the signup portal to create a new record (last row). AthleticDept.fp7.zip

Recently I helped another forum member with an IWP issue and related images. The attachment simplifies the problem to help analyse why IWP portals can't display graphics from a secondary relationship. My sample has three tables, TableA, TableB, TableC. Each table is linked in a chain A --> B --> C so that a layout based on TableA can use related data from TableB and also include secondary related data from TableC. I agree this is against all the rules for data in a portal (but technically it should work), however the relationship and portal works in FMP, but the IWP version cannot display the graphic from TableC, even though it can display a text fiel…

Hi, I'd just like to check with you that what I'm doing is OK. I'm trying to count "joins / relationships / links" that don't happen. Example products that haven't sold = no product ID in the sales table. I've pasted an explanation pic here at It's pretty straightforward. My way of counting the "missing links" seems to be too good to be true. Is it ? TIA Bobbby

I'm relatively new to FileMaker, but I have created a rather complex database for a client that holds information about doctors that they have interviewed in one table. Another related table holds individual records for each interview that was conducted which includes the date and the honorarium amount paid to that doctor. The client wants to be able to click a button, enter the year and have the database search for interviews done during that year and, along with the doctor's contact information, show the total of honorarium payments made to that doctor. I tried a relatively simple approach to this by creating a new table to hold the information found when the…

I am trying to create a layout for my database that will have 2 portals from the same table. I created 2 table occurences of the same table, but am having trouble getting the information I want to show in my second one. There are 2 original tables: Customers Log and Production Goals. Customers Log has these fields: Date Actual Start Date Completion Week Completion Job Sold (all fields = SOLD) Production Goals has: Week Installed Year Installed Weekly Goal Job Sold (all fields = SOLD) They are linked by Week Completion = Week Installed, so I can run a report to see what our weekly vs. actual was. I have a portal in the Production…

Hi in the attached file I'm trying to have a bookmark scrapbook, enter the url's in the portal switch to the website tab and choose the links from the current record. The problem is when I try to browse a link from the value list lookup, it shows the value list links from all the records, and not only from the current record! Please see the file. Am I right that it has to do with TOs? LOG: 1 PASS:2 Thanks Clone9v20_copy.fp7.zip

Im working on a stock inventory database. Basically, with three (3) tables namely: 1. Stock 2. Stockline 3. Requisition Im having some problems in connecting the relationships for the 3 tables. I was hoping that in stockline I can pull-out some datas from stock in organizing my inventory. For requisition, I want some data to be pulled out from stockline and stock using drop-down list. If someone can guide me on how to achieve this. I have provided my file open for your scrunity. Inventory.zip

I still somewhat new to FMPro. I hope my question makes sense. When setting up table relationships, is it possible to economize table occurrences? For example, if I have the following relationships: Relationship A. Part table --> Image table Relationship B. Sales table --> Part table When creating a Layout with Relationship B, I'm not able to access the Image table defined through another relationship (in this case, Relationship A). Is the only way to do this is to add the Image table occurrence to relationship B as follows? Sales table --> Part table --> Image table Would adding table occurrences this way …

Apologies for my lack of knowledge.. I have "table a" with fields A B C D E F that all contain records. I also have "table b" with fields A B C D. A B C all contain records consistent with values in "table a". How can I make field D in "table b" get records from "table a"? Help much appreciated!

I am having a problem with a relationship between two files. In doc A I have a field with name and an identifier with the value "U". In doc B I have a list of values by persons, plus a calculated field (ID) marking the unique values for each person (using a self-join relationship), making a list of unique values for each person in the table by searching on person name and ID=U. Follow me? Now: In doc A I would like to have a portal, displaying this list of unique values for each person, meaning that when I am on John Doe's record in doc A, I would get a list of the unique values related to him in doc B. To accomplish this I made the relationship: person = pe…

Situation: Orders are placed for software licenses and software discs, and from those orders inventory records are generated. There might be 30 licenses and 2 corresponding (pieces of media, like CD-ROM) discs placed as an order. Each disc has a physical location (room) it is kept in. When someone wants to install an application from disc, she finds a license record (e.g., "FileMaker Pro 9"), and sees where a corresponding disk is located. A license doesn't technically own a disc, nor vice versa, but they have an association, i.e., a FMPro9 disc is associated with all the FMPro9 licenses, and vice versa, but is not associated with a "FileMaker Pro 8.5" license. I've…