Relationships

I want to grab all related records into a calculation. In theory I could do this with a repeating field by having MyStuff = Repeating[1] & Repeating[2] & ... Related records are much more elegant but I can't get it to work. Interestingly, the following works for the first and last related record Mystuff = Tbl::Field & Last(Tbl::Field) But the following doesn't work Mystuff = Tbl::Field[1] & Tbl::Field[2] & ... (neither does Mystuff = GetRepetition(Tbl::Field,1) & GetRepetition(Tbl::Field,2) & ... ) Ultimately I want this information printed out, but I've got multiple fields in the related record…

Okay, I've been using FileMaker for years...since version 4 in fact. I'm pretty sure, however, that I could be doing things much more efficiently. Let me give you an example: I often have the need to create a related record in a separate table through a script. Here's what I do: 1. Copy the ID of my parent record 2. Go to layout of the child table 3. Make new record 4. Paste into the appropriate ID field to complete the relationship. 5. Commit the record 6. Go to layout of the parent (original layout) Suggestions on a cleaner way of doing this? Can anyone point me to similar basic topics or best practices. I'm getting things done, but I can't he…

Hi there! This is Nopposan again, and I'm feeling just great guys, and I know I'm just going to get a bundle of kicks out of any solution you care to enlighten me with. Whew. Well, I just need to know if it's o.k. to rename a table that I've already created relationships to. I mean, is renaming it going to break all its relationships by making the table incognito? How about renaming the database file the table is in? My tables are all in separate files. Thanks, guys. Cheers.

I don't know how to ask this without the picture! I have never seen this before. On my "SS Quotes" table, next to field Status does NOT have the 3-prong branch like the field below it (Year Month). What does that mean? Thanks in advance.

I have a school database file with 12 tables and 1000+ student records. The main table is Students and the other tables include Parents, Classes, Teachers, Enrollments…etc. What is the best way to structure the file relationships so that only the 350 currently enrolled/active students and their related records show when working in the database. I don’t want to have to archive/export the inactive students to another “Archive” file. I’m thinking that I should probably flag each record in the Students table as “active” or “Inactive”, but not sure how the related records from the other tables will show for only the active students. Any advice will be helpful. I’m using F…

Hello, My database is used for a webstore. I have a table [ data] and one [user_info]. The links: [data.order_id] = [user_info.order_id] Then in the [user_info] table one or more records per order. I only have one or two records per order that are of interest. One is billing adres [user_info.address_type]="BT". This is standard. If the shipping adres is different there will also be a [user_info.address_type]="ST". In my overview on the left I display billing adres and on the right the shipping adres. If there is no "ST" then "BT" is to be used. I have made it to the state that it collects the right info but only one is displayed. If no "ST" exis…

I have seen the wisdom of providing a unique ID for each record in each table. I've seen it a little late though. I don't have any big problems yet, but I'd like to give unique serial ID's to more thatn 3000 records that are already created. I tried to do this using a script that looks like this: Show All Records Got to Record/Request/Page [First] Loop SetNextSerial Value [Patient_Info::PatientInfoID.pk; Patient_Info::PatientInfoID.pk+1] Go to Record/Request/Page [Next; Exit after last] End Loop Unfortunately, this script skips more than 1000 records, begins with the serial number 3 rather than 1, and proceeds to label 3, 4, 7, rather than 1, 2, 3. …

Hey guys, I am trying to dynamically sort a report with controls that are in the header. And the report items themselves are in two related tables. I have a drop down list to select a company and I want every record for that company to show up in the report, however only the first record shows up. Please check out my file at http://www.mediafire.com/?ifdlzjdrgn2 and check out the Invoice Report layout to understand what I am talking about.

i have 2 files. the 2nd file has "symbol", "price" , and "date" as fields. a symbol will have different prices at different dates. the 1st file has a "symbol" and "date" field and needs to look up the "price" corresponding with both. I have always done this by relating to one field in the 2nd file. How can i relate to 2 fields ("symbol" and "Date")?

Can someone help as I'm a basic FM user. I have a lookup between two tables driven by ID number. In table one, I have four fields: Name 1, 2, 3, 4. Now in table two, when I enter the ID number, I want to see all four contacts in a Pop-up menu so I can select only one… Thanks, Denis

Hello, all. Total novice here. Please tell me how I can be sure that when I import records to my database table they will automatically be created in a related table. I have two tables in separate FileMaker database files. I've related them via the relationships gui map that's provided; I've created a relationship matching/equating ID numbers that allows records to be created in one table whenever records are created in the first table. However, I suspect that when I import new records into the first table from an Excel file those records aren't automatically added to the related table. I have hundreds of records that show in the first table and not in the…

Hi I am building a database (my first) for my plumbing business. I am using the missing manual book as a guide and although its very helpful I have many queries. Firsly I am trying to work out what is the usual solution to tracking the status of my jobs from a quotation through the stages of active ,completed unpaid, archive etc. Would it be wise to make a "Job Status" value list for this or does this require a script, or am I missing the point altogether ? Thanks for your help

I have a database in Filemaker 8.5 that tracks Accounts in the main table, transactions in another table, and the account number is the field on which they are joined. Each Account has anywhere from 0 to 200 transactions. My problem is that some Accounts seem to have 'phantom' transaction records showing up in the portal. The portal is on an Account layout, and the transactions are sorted so that the newest transaction is to be at the top. When I try deleting the bland transaction record, the record count for transactions within the account is decremented, but the overall count of transactions is not decremented. Other facts about the database - there are other ta…

hi. i would like to prepare a contact database for a magazine subscription with the possibility on invoicing. i am already stuck with relationships! any available sample?

I am building a database project for an academic. There is a central master database that acts as an overview/relater/controller for the other external satellite databases that hold real data. One of her needs is the ability to add typing tags to the individual records in each individual external database. The interface in each location is 5 fields arranged hierarchically so that she can set each tag and a calculation field joins them together as one long tag. This field is then used via relationships to join all the differing based on her arbitrary needs. The first challenge I have and the one I am going to ask about here is the need for synchronicity. What I m…

I am trying to have merge fields display correctly on a layout. I can get it to work on layouts that are set to display data from the original table occurrence (the one that was created when I initially created the table), but when I try to get them to display on a different table occurrence (in a different TOG), it just displays thus: <>. I am careful when setting up the merge field that I select from the same table occurrence that the layout is displaying data from but it will not display. Is this a limitation of the application? I can't find anything in the Help files to confirm/deny this. Thanks in advance for all replies.

I am trying to setup a many-to-many relationship. What I have is 3 tables, the Distributers, the Manufacturers and a join table. A distributer can represent many manufacturers and a manufacturer can have many distributors. What I'd like to do is show a manufacturer record with a portal showing a list of it's distributers. And a Distributer record showing a list of it's manufacturers. It would be ideal if you could create a new distributer record or a new manufacturer record by entering it in the portal. If this cannot be done this way, that's fine, but at lease be able to assign existing manufacturers or distributers to a record via the portal. I've rea…

I'm trying to make a database to help me with scheduling teachers and classes, and I'm stuck at the initial database design. I have 6 periods, a bunch of classes, and a bunch of teachers... in each periods there are multiple classes offered and multiple teachers in each class. What I want to be able to do is have the database generate a list of classes for each period and a list of teachers for each class in such a way that I can create a schedule for the staff. So for each period, there can be multiple classes, each class can have multiple teachers, but each teacher should only be in one class per period. I thought that 3 tables were necessary, a periods tab…

Hello, You guys are an excellent help. When I have a question, I simply run a search and find the answer in less then 5 minutes. This one, however, I can't seem to find. Maybe someone could help. I have setup a table called 'Settings' to store windows sizing etc. The problem is pulling the data to set Window Sizes, etc. I have one record in the 'Settings' table that will hold everything, but I don't know what is wrong. I currently have 'Settings' related to 'Interview' via a field called 'Constant (in both Tables) which contains the value of 1. I can do a lookup without any problems there. I've tried the same thing with other tables, and found I did not ha…

Hi All, I have a database with 1 main table and 10 related tables. The main table has around 200 layouts and I have just built a navigation menu which effectively has 10 tabs and on each tab is a different portal to a related table. On each portal are 4 fields. So all up the navigation menu has 10 portals and 40 fields. My problem is that I want to copy this navigation menu onto each of the 200 layouts but when I do so the fields in each portal are blank. It will be a massive job (some 8000 fields) to reconnect the fields to each relationship on each layout. One thought I had was to make the navigation menu a separate pop-up window but this does not real…

I'm not sure if this is the best forum for this question, so apologies in advance.... A quick summary of the problem: once there were only retail customers - now there are wholesale customers, too. A client of mine bought FMP8 and copied a sample tutorial database for managing Orders, Inventory, and Customers. She soon realized she wasn't up to tweaking it to meet her needs so she hired me. I added a line-item portal to her Order layout, defined value lists of customers and inventory and attached them to pop-up menus, and so forth. She navigates through the layouts simply by selecting them from the Status area - there's not a script to be seen anywhere.…

...Or at least I think so. I have four related tables: Services, Labor, Difficulty and Service Items. Services is the main table and is a list of possible services a vendor can offer. Difficulty is a related table that contains related records that help populate a dynamic value list. Each service can have it's own method of defining levels of difficulty. It could be a Simple Medium Complex set or perhaps intensive, non-intensive. Each difficulty schema is given a name identifier which becomes the match field (e.g. if "S/Med/Cpx" is selected in the Services Difficulty Scheme field, the value list in Service Items will be populated by three values, Simple, Medium …

Hi All, I have a new project, and I find that I am stuck. I have this issue basically solved in a spreadsheet, using pivot tables, but it is not suitable for the "Data Entry Personnel" interface I really need. I have basically 5 source tables related so: PARENT = JOINTABLE1 = CHILD = JOINTABLE2 = GRANDCHILD Where for problem solving purposes, PARENT is a list of "Events", JOINTABLE joins those events to CHILD which is "People", JOINTABLE2 joins those people to GRANDCHILD which is "Materials". I have the summary for Child set up easily, using a self join of JOINTABLE1 to get the summary of CHILD for each PARENT. (Where I summarize all record…

Me again, sorry need a little help. Here is the stripped down version of my file with the necessary parts for my question only (or so I believe). Question: Items table is related to LineItems via Auto-enter serial number and LineItems is related to Purchases via Auto-enter serial number as well. Due to needs of people it is set up so that multiple companies can go onto one PO. Basically a user enters ItemCode into portal and the next field is a list of available Vendors for that item. Once the Vendor is entered Primary key is looked up (via auxiliary relationship) and the rest of the information is automatically filled in. All of that works perfectly (…

Hey, So I have a solution where we identify each user by a 3 digit number. I have an 'employee' table that has the three digit number plus all the other employee info. I use this table as a look up table ALL THE TIME so I can portal their full name or short name into reports, layouts, etc. It works pretty good, the employee enters "123"and Bob Smith automatically appears. The question I have about the design is that I use this method in virutually every table I have as each employee creates records in the tables. As time has gone by and we've added more tables, each table is inadvertently related because of the Employee table, which isn't causing any problem…

Hi, My name is steve and I am trying to use value lists from a field. That much i can do. But what I would really like to do is to have one field as a value list and on the basis of that choice be able to get the next field to reduce its options. I have a feeling it is to do with value lists based on a second field and setting up a relationship. I have to admit to fumbling around in the dark.

I have a listing of expenses and I want to be able to view them by year. I want to create a drop down menu (value list?) where the user can pick all, 2005, 2006, 2007, etc. And only those expenses for that year will appear on the listing. What is the best way to set this up? The thing I'm afraid of using a value list is when 2009 comes around, I'll have to remember to include that year in the drop down menu. I'd like the picking of the date of purchase year to be automatic. I hope this makes sense. If not, please let me know. :)

Hi everyone. Just a quick question: I don't see an option for a primary key in FileMaker 9. I'm assuming FM uses slightly different nomenclature for it. So, what is FM's equivalent? Where do I find it when creating tables and fields? Thank you! Jd

Hello, More or less, I am a newbie with filemaker. I have received an assignment to develop a database. Had no prior training with the software besides from looking at training tutorials. In one of the tutorials, a database with relationships is laid out. Here is the scenario: A table contact has a foreign key from table client a.k.a. clientID. Foreign key (clientID) in table contact is related with an instance in the database called contact_client.companyName which contains one field. My question is, what is the use of the described scenario and how do we make a use of them. Thank you for any help, Tina

I apologize ahead of time if this is the incorrect place to put this issue. I'm having an issue with direct client connections being slowed down by image links. In some of my more heavily trafficked layouts I have a direct link to a PDF database. Essentially, the problem field references a "Files" database by using the simple "::DOCUMENT" reference, where :DOCUMENT is a validated container. I'm aware there are several better ways to do this, but which should I use? I don't need the PDF to display within the layout, but I do need it to display the title and be able to export. Any suggestions? Thanks. Edit: I'm trying to avoid any third party programs like sup…

Hi everyone, I'm sure this is easy to do and I'm just brain-blocked, but here's what I need help with. If I do a find and select certain records in my database (constituents) and then send them an email, I would like to be able to quickly and easily mark their records that they received that email. My database is set up with a constituents table and an events table. It's the events table that should be updated to indicate the new activity. I've been trying to do it through a portal using the Replace Records function but I realized after the fact (playing in a non-live version of the database) that wasn't what I wanted. Can someone please point me in …

I've found some useful threads explaining how to use multikeys in relationships, specifically how to use multiple calc fields as foreign keys. For example, one of these calc fields called cEnrollment might look like this: GetNthRecord(students::studentName;1)&"¶"& GetNthRecord(students::studentName;2)&"¶"& GetNthRecord(students::studentName;3)&"¶"& "ANY" Here's my problem: Because the calc field must be indexed in order to be part of a foreign key, it can't be unstored. Consequently, whenever a student name is added or modified in the data entry layout, the cStudentList calc field does not 'get updated' with the new info. This is a…

I have a database to maintain bill of materials where it contin part description, price etc .There can be price update for each part. How can i maintain price update history. Also i want to display this price history in a portal which is view only purpose. Anyone please guide me on this issue?

Here's my situation.... 2 filemaker 8 files(work orders and clients) Each record in the work order database has a -Client Field -Hours Used Field Each record in the client database has -Client Field -Summary hours field In the client database I have a relationship that uses the client field to find matching records. Then the summary hours field adds up the hours used. Seeing I have 1000+ work orders for each client this takes filemaker awhile to calculate the summary hours field. Is there a different way of doing this that wouldn't take so long??

I am a FM9 newbie and am struggling to get to grips with the basics of the db design. I have inherited a "contacts" table showing all contacts related to our company, whether they be customers or suppliers. From a "contact" layout I can generate a new customer "order" using the contact info. My problem is that I need to include a portal with the ability to select using a drop-down list of filtered contacts (staff) who will work on this order. I have a feeling that this can only be achieved by creating a seperate table for staff rather than holding them in the same table as the customer contacts. Am I correct or am I missing the point?

My next project has started and I'm struggling right from the beginning with a relationship. We will be collecting participant/patient information as part of a study we are doing. I'm trying setup it up so we can see immediate family and extended family. I've got it close but a few things are tripping me up are; Fathers having children with more than one wife mother having children with different husbands. And I have no control of the order in which I get participant information. So I could get info about a child before (week/months/sometimes never)I ever get their parents info. Unless I'm misunderstanding I can use a portals to create "parent" records from …

Hello All, I had a few questions, I'm a new FM Pro user and have Version 9 Advanced. I would like to create a few different DB's and used them to populate fields in Value list. For example I would like to have a DB where I can add Car Make Model and have that information populate into another DB where it is needed. I can't seem to figure out how. Please let me know of any ideas. Thanks

I have a layout, let's call it the 'Student' layout, that has two portals. That layout displays records from the student table and the two portals display records from the courses and tutoring tables (see image A). What I am attemping to do is use information that is entered in the courses table via portal on the student layout, to create records in the tutoring table. The tutoring table uses a vaule-list based on the course id (see image A). The problem is, everytime I enter a course in the tutoring table, a new record is added to the courses table, with just the course id. I don't want that. Please help! Image A Image B Image C (Relationshi…

Invoices come in three flavors: "Sale", "Order", and "Staged". These are mutually exclusive, and the flavor is stored in Invoices::Flavor. I want a layout that shows only Staged. So I create a self-relationship from Invoices to Invoices_Staged where Invoices::uc_Staged = Invoices_Staged::Flavor. uc_Staged is an unstored calc "Staged". Then I create a layout based on Invoices_Staged. It's showing me all three flavors! Is my approach wrong? Thanks very much. Chap

I have a layout with a portal. and I want my user to be able to click a button and copy not only the data from the current record, but all the related records into a new record. Can anyone help me with this? So main table: Physician Order with the fields date issued, physician name, etc. Related table: Short term goals, linked to Physician Order on Physician Order ID. One current Physician Order can have multiple short term goals, and we want to copy the current Physician Order with short term goals, and just have the user change the Date Issued field. I am pretty new to Filemaker (sorry). Thanks!

Before I start, can i apologise if this is in the wrong area of the forum. I didn't know where else to put it. I'm a complete newbie to filemaker (teacher in my spare time) and i'm trying to setup a mark/grade book to store all of my pupil's marks in. The problem is that I could really do with some advice on the best way to structure it. I have 18 pupils and I will give each a grade for each piece of work we do. The problem is that i need to add new pieces of work as I go along. I can't just setup 15 fields for 15 pieces of work. I might end up doing 20 pieces. I hope this makes sense. I'd be grateful of any advice.

Mystery Error message when attempting to delete a record. This operation cannot be performed because one or more required related tables are missing. I have a layout/table that has only 6 fields. Customer number Zip code Monthly Average Sold which is a look up from another table in another file entered when a zip code is entered Rate which is calculated based on various selections in another table in the same file for each customer Monthly Cost which is product of Monthly Average Sold and the Rate and the calculation is …

Hi, I am trying to build a standard invoice solution, the only difference being that the quantity for every purchase being fixed. Some more details: I have a COSTUMERS, INVOICES and PRODUCTS tables in addition to a LINES table. The database is standard with a relationship between COSTUMERS and INVOICES and LINES as a joint table between INVOICES and PRODUCTS. But there is one relationship that I can't work out. I want the quantity to be dependent on both COSTUMERS and PRODUCTS. So if costumer X buys product Y, the quantity added into LINES (portal) should be inserted automatically. I am not able to sort out the relationship. I have created a table called …

Yes I know, I chose that heading specifically because I knew it would get moans and groans from the community! I have a problem in that in my STOCK table we often have items (computers) as a single line item including a unique id number as well as the individual machines actual serial number. We often sell multiple computers (eg 3) to a single customer which is then allocated to a single Invoice (there we have the [color:purple]Invoice & [color:purple]Invoice Table combination). Therefore I need to connect these MULTIPLE records from [color:purple]Stock to a SINGLE record in the [color:purple]Invoice Table. I include files so that it can be better understood (mod…

Hello All, great to be part of the Forums. I have just started working on a FM9 database and am trying to relate 1 customer to multiple inventory items which is also related to another point of sale table and delivery table. The layouts and relationships I've been trying to create ultimately turn into dead ends. I've attached a couple screenshots of what I have, one is the layout with one inventory item which I don't want new inventory items, just to relate multiple records from the inventory table. The other attachment is my relationship graph, any help would be much appreciated. Thank you.

I have three tables AR -- field a TK -- Field c Field d PJ -- Field e Field f Relationships AR: field a -> TK: Field C TK: Field d -> PJ: Field f Tk: field d shows PJ: field e through a lookup I want to show PJ: field e from the relationship between AR's relation ship to TK's. I am not getting the correct value. Why? Thanks, Rob

I apologize if this question is too basic. I've noticed that Similars are used in tables.. but have yet to find out exactly why and what they are.. Is this a FileMaker thing.. couldn't find any explanation in my FileMaker book or in the Help menu. They seem to have to do with keys... thanks for your help with this. Jean

Hello! I've searched at some length trying to find some clue as to whether or not I can even do this without a workaround, so I'm asking. My apologies if it's something that's already been covered. I'm developing, as part of a set of databases, a contact management database and a purchase order database. On the PO database, there are three address sets...Supplier, Bill To, and Ship To. On each of these, the client would like to have auto-fill functionality. The problem is that when I create a lookup that connects the PO company name to the contact company name, it auto-fills all three instead of just the one I'm filling in at the time. Is it possible to create m…

Hi, ok this is the setup: Tables: Items Products Purchase_Line_Items Purchases They are related as follows ITEMS (Item_Code) = Products (Product_Code) Items (Vendor) = Products (Vendor) Purchases (PO_Number) = Purchase_Line_Items (PO Number) Purchase_Line_Items = Items (Item_Code) Purchase_Line_Items = Items (Vendor) I am trying to display a portal with Purchase_Line_Items data from Products table. I got the portal up and running and it displays for example all records of purchases for one item from one vendor - that works correct. What I want to do is for the portal to show purchases for one item from ALL vendors …

I have purchased a Merge Table with close to 500,000 records. I have built a table and setup the relationship necessary to make lookups, calcs and portals work and it is working. However, interaction with table is SLOW. The table has [Main DB]zip5 == [Zip DB]zip5 (5 digit zip) [Main DB]zip4 >= [Zip DB]zip4low (4 digits/lowest in the range) [Main DB]zip4 <= [Zip DB]zip4high (4 digits/highest in the range) The three target fields in the Zip DB are fully indexed. Pulling info this way is taking up to 25 secs per lookup. I am wondering if anyone sees a way to speed this up. Anyone have any ideas? Jamie

Alright, it has been a while since I have worked with FileMakerPro. For some reason I am having a terrible problem with getting my table relationship to work. Here is the file. Please look at it and advise me on what I am doing wrong. Thank you. KeyDatabase.zip

I searched first ... no luck. If this is the wrong forum, please move this, Mods. I recently upgraded FMP 3 to FMP 6 and I received FMP 8.5 in the mail ... but haven't installed it yet. It would be very cool if I could somehow tell my Mail to grab its e-mail addresses from FMP 8.5 ... instead of (essentially) having Address Book just sit there as a e-mail address repository which *does* talk to Mail. (FMP is what I use for my Contact Manager ... a layout of my own design because ACT! and other packages had more of what I did NOT want and not enough of what I did want.) BUT ... on a "Geek Scale" of 10, I'm sure I don't even rate a "1". (So be gentle, please.)…

I am trying to make register like Quicken for accounts. I have got the calculations right. Now i want that user should enter only in one of the fields(Debit or Deposit),Eg- if user enters the value in deposit and again user is entering the value in the Debit field the previous field should get clear and vice versa Thanks

I have an old friend in the Filemaker biz, one of those Amadeus types who assimilates everything Filemaker develops at the maximum level of potential, but I have since fallen out of touch with him. WHERE can I find guidance on sophisticated relationship structuring as relates to separating data from interface or even running calculations through separate relationships (to maximize performance and modularity) :? Anything here in the forum? books? other websites? Thanks in er..."advance" for your help.

I am a Landscape designer and use filemaker to a limited degree to create my estimates & invoices. I have been estimating with both material & labor added together as one item for my estimate. I would like to start creating “Components”/ kits to put together groups of material, labor, subcontracting, & equipment. My question is, I am looking for a simple example of the relationships of Invoice, Line Items, Components, and Items to get me started and build off of. Keep in mind that I might sell Items to a customer only (ie. Sugar Maple delivered) and need to put an item on the invoice without being a component. Thanks in advance, Con

Hi, Any comments will be appreciated. I am really stuck. I am a biologist/bioinformatician. I am trying to track info for mices, in the lab. 1-)A mouse can be part of multiple breeding events. 2-)And, a breeding event can contain multiple mice. This relationship goes over mice::breeder_ID and mice_breeders::ID, as shown in attached image. The problem is, I can not have both of the above 2 points. I should choose either of them. From the mice layout, I want to put a portal which includes the information regarding in which breedings that mouse was used. And, from the breedings layout, I want to put a portal, which shows which mice wer…

Hi all I have convert an old FM6 program to FM 8.5, the convert file works fine. I am redoing the program to take advantage of some of the new features in FM 8.5. But I have been having a lot of trouble with the lookups. I have manage to solve all the problems but this one. I have retype all the data at least a dozen times and it will not work. This is really madden. I think the relationship is good becuase sometimes the field information is correct and other times is not. I have attached a file. The main file name is wpqentry_form. Thanks in advance Lionel wpqentry.zip

Well, after much work and combination of different types of functions, I was able to get the results in a field I needed to do the type of relationship I needed between 2 tables. Then, much to my surprise I finally realize my relationship won't work -- I'm using a calculated field on one side. Then I find Soren's post about how to do a relationship with a calculated field. http://www.fmforums.com/forum/showtopic.php?tid/187182/post/252366/#252366 So, I'm confused. Is this something new with 9.0? Please and thank you Christopher

This is the first time I've done an attachment, so I hope I've done it right. I thought this should be easy, but I can't seem to figure out what I've done wrong. I have a customer table. I also have a resources table. The resources table has a Customer Type field that's saved as a global. Its value comes from a value list. The customer table has a similar Customer Type field. Its value comes from the same value list. I can set the customer type in the resources table. I want a layout that shows customers whose customer type is that set in the resources table. I would have thought a table occurrence and a simple join with the resources table w…

Hi, All, Sorry if this is going over old ground, but I've been dumped with the task of creating a "simple" database for the company I work for. The design stage is easy enough - I think! My problem is trying to get 6 fields to add up. I have 6 fields designated for numbers in a column and a 7th field where I need to get the total of the other 6 fields. I'm convinced that it's oh, so easy, but I haven't got a clue how to do it. All and any help greatly appreciated! Cheers, All. Juttle

Hi, again, The second problem I'm having (thanks, Lee, for sorting the first one out!) is duplicating fields. What I need to do is produce a tear off slip at the bottom of a job sheet that contains stuff from the top part of the sheet. For example, I need to be able to enter the clients name in a field at the top of the form, and also have it duplicate itself automatically at the botton of the form. Same with date, hours, and invoice total, all of which appear in the main form but need to duplicate themselves in the bottom section. Am I explaining all this stuff correctly? Anyway, that's my problem, any ideas, Guys? Cheers, Juttle

I can't get data from two tables to display. Any insight into what I am doing wrong here? Attached are two screen shots that will hopefully make what I have done clear. Shot 1 shows two simple tables, and how the primary and foreign keys were set up. Shot 2 shows how the relationship was set up, three layouts (table 1, table 2, and a combination of the two) and the "result" that only shows data from table one. Thanks you for your help.

Hi, I have 2 tables: Contacts and Clients The two tables are related by the field Company which appears on both tables. When I create a new Client and I pick the company name I have a portal on the Clients layout that displays all the Contacts that share the same Company Name. On the Clients table I also have a field named "Shipping" that contains the value "Shipping" as auto entered. On the Contacts table I have a field name Type that can be set to empty, Main or Shipping. Finally there's one last relationship named "Shipping" that shows me in a single row portal on the Clients layout, the contact that shares the same Company Name and who's type is set to "Ship…

I have 2 filemaker tables One contains a list of available URLs, The other contains a list of keywords to help us find a relevant Domain Names for our clients. As an example, one of the keywords could be "wine" another could be "vinyard". Using an exact relationship between the two tables (and ignoring the .net on the end), our URL list would pick out "wine.net" and "vinyard.net" for example, but not "winecellar.net" or "italianvinyards.net", because they are not exact matches. Is there a way I could make the table relate to records that contain these keywords, when they are in amongst other characters, not just an exact match. i.e. simlar to *vinyard* in a…

Hello I am a newbie and I am having a very hard time getting my head around a ,probably, very easy logic. I have 7 tables. 1 is contacts (freelancers), another is clients and the last 5 are a collection of companies. All the contacts can work for any of the 5 companies. Any Client can work for any of the 5 companies. In the 5 company fields, there are 10 work posts that are populated from the contacts table. Yet each contact can have multiple and different roles depending of the project. I have managed to create in the Client table layout a portal via a client_ID from each project that shows which of the 5 companies the client has worked with. I am trying to apply…

I have four tables and a Join Table. For one piece of equipment, there is the possibility of three Vendors for the same piece of equipment with the same RFQ No. I am trying to achieve a portal or list where the one RFQ is listed i.e. RFQ 1001 so i can design a report where i can click the one record for RFQ 1001 and have all information for the possible three vendors print out. Is this possible. Please see example file attached. JOINHELP.zip

Hello, I'm working on moving my personal time invoicing system from Excel spreadsheets to FileMaker Pro 9. I have had some experience with Microsoft Access, but the lack of query objects in FileMaker is a bit confusing. I would very much appreciate some suggestions for how I should get started. I have defined two tables: (1) Projects, which contains only a unique numerical id and a textual name field (2) Billables, which contains a date field, textual description field, and numerical field to represent hours. Each record in the Billables table is related to one Project id. The idea is to use the Billables table as a time sheet. Every time I complete some work on…

Hello All, I am a very new individual to FileMaker, and have been tasked with the responsibility of making a database for the graphics production flow in our company. Here is a brief description of what I am trying to do. I have a database comprised of graphic panels. Each graphic panel is comprised of several elements, all of which are unique except for "images". There could be any number of images that comprise the content of any graphic panel. I've alloted up to 12 container boxes per record for these thumbnails. My containers are "image1" "thru "image12". I am making another layout that is just a detail of that particular thumbnail. It seems like I n…

I've had this need several times. I've attached a file to make it easy. Need: One parent has several children. If the account number on any of their children is different than the rest, I want to produce an error (unstored calculation) in the parent. I do not know what the account numbers may be so I can't hard code for it. I don't care how many different Account Numbers there may be ... error is error and only ONE different produces the error. All I will know is that EVERY child should share the same account number. I've accomplished it in various ways in the past that I'm too embarrased to explain. I'd like to find an easy, sweet way to do it (hopeful…

Hi everyone! Iâ��m trying to build a form that will allow the user to select specific features for a camera while preventing the user from selecting the incompatible features. Iâ��ve tried setting it up a few ways: A) Separate tables for each feature (only two fields in each) with a relationship to the previous table to create a relationship chain that would only access the correct information. Separate tables for each feature (each with a field for every feature) with multiple relationships connecting each table. C) One table with self-join relationships between each of the features. Each time, the first step between â��Cameraâ�� and â��Tape S…

Hi, Complete beginner here, I'm afraid. What I'm trying to do is establish a simple (at least, I think it's pretty simple) procurement database. What I want to do is to be able to 'check in' equipment from the database into a series of discrete locations. In terms of reporting, I'd like the database to be able to give me a breakdown of equipment in each location, a total kit list, and a breakdown by supplier. This way I can make changes to price, location and specification, and it'll be instantly reflected in the PO I give to the supplier. I've been through this process before, using Excel, and it wasn't much fun. So far I've got several tables. The main one is …

Hi there, I am learning the Filemaker ropes and need some advice. Not even sure if I am in the right topic section. I am trying to create an application that manages property rentals including the payment of utilities. Each property has fields, 'rent from' and 'rent to' eg 1st Feb 2008 to 29th Feb 2008 and 'current' and 'previous' electric readings. I want the user to perform a search for the property, this brings up portals with all the previous payment records. They then click a button to create a new record, the new record is pre-popluated with some of the data from the previous record. Specifically I want the data from the 'rent to' to go into the new 'rent from' fie…

Ok, I'm a total newbie to DBs (well played with them about 20 years ago) What I'm trying to do is have/connect 2 database files. One for customer contact info (ie: address, phone, etc) the second for the customers' machines. For the machines db, there are different models, ammount of machines, and would also like to add in the service history. So my basic question(s) is do I need multiple files for this, or can a single db do it? I'm looking to display a table that has a drop down combo box of the machines that, that customer has (along with serial#s, etc.) How/where do I link the 2 files? I have the relationships there, a drop-down box tha…

Hi, I'm new here, so if I mess up posting or don't use the right lingo please just let me know. I was wondering if there was a way to create records in a table through another table without using scripts (I'm new to FMP and I haven't quite gotten to that point yet.) Background: I'm creating a DB for my family shoe wholesale business. When it's done, multiple people will need to be able to use it...on a single computer so no networking is needed...but it must be easy enough for people who are barely computer literate. I've already created PO forms that create, through portals, individual purchase records. I've done the same on the sales end (an invoic…

There is a post Here to which I have an almost-identical question but focusing on different results - in my case the multiple databases are different but related by links to (in our case) Projects. People, correspondence, inspections, project data, and so forth are all related by project number. The information is different enough that separate tables (files... we built this system under version 5.5 and have brought it forward into 9). I was asked today if a search was possible across multiple databases - returning the hits regardless of where they were found (for example, identifying all the reports, inspections, and correspondence for a particular …

I would like to build a simplae office stationary database. At present when a staff takes items from the receptionist, the receptionoist keeps a track of it. Below is what she keeps track of: Date taken: Taken by: Department: ( we have 8 departments such as Audio, Video, Graphics etc) Item Taken: Quantity: Purpose: I have come up with the following tables and fields: 1) Department Table: Department_id (pk) Department_name 2)User Table User_id(pk) Department_id (fk) User_name 3) Item Table Item_id(pk) User_id(fk) Date_taken Quantity Purpose Department I am not sure about this relationship. Is this correct?…

Hi all,, I have a problem with portal. When I set up relationship between the two tables, and add portal on my layout. I can add new related records using the portal. But as I add more than one condition to the relationship and try to add new record in my portal I get a error message saying: "This action cannot be performed because this field is not modifiable." However I found out that I can change and modify existing records, so it seems that only creation of new records is forbidden. I have checked the option "Allow creation of records in this table via this relationship" Just to explain why I need multiple conditions: I have two tables: Employe…

I have a database of items. with categories: Procedures Lenses I also have some Procedures flagged as "favorite" with a flag field. I enter 1 if I want it marked as a favorite. I'd like one drop down, accessed by script (for example, button to show all Procedures or button to show favorite Procedures) is this possible? I'm really racking my brain here. and can't seem to find out how to define the relationship to be able to show the favorites. thanks in advance!

How do I create what I think of as secondary relationships? In an event management db, EVENTS is related to PARTICIPANTS via a join table called REGISTRATIONS (EVENTS/event_ID is related to REGISTRATIONS/event_ID and REGISTRATIONS/participant_ID to PARTICIPANTS/participant_ID). Each event has several meals (each meal is, of course, eaten by many participants) and also several nights' lodging for many participants) and I don't know how to relate them (meals and lodging) to events and participants, other than realizing that I need a join table for each (something like MEALS_eaten and LODGING_nightsSpent). I'd very much appreciate if someone could point me to …

I heard a lot about not using globals, but I am still finding cases where they seem to be the best, sometimes only, solution. I have included an example of a current case. I am attempting to setup a report, or series of reports, based on the chosen expiration month and year stored in the center table. The only other way I see to do this is to filter each table as a display that particular report. Any thoughts or suggestions? Bill

Please help me to figure out Relationships and see my attached pic and database file. Here are my related issues. I think i have to put in "Link" Tables? but not sure how to (What fields to put in to them) and how to link them to overall tables. 1. I have unique individual Project Numbers associated with Clients although the Same Client may have different Project Numbers. 2. Individual Project Equipment would have unique Request for Quotations (RFQ) but some RFQ's may have the same Vendors. 3. Individual Projects would have unique Purchase Orders but some Vendors may exist in different PO Numbers. Hope i am making sense above. Sully SullyF…

Hello everybody. I have looked and looked and I have not been able to find any guidance, tips, articles or example files on how to design a knowledge base. 1. If you know of any such (free!) resources, please post them here. 2. I would really appreciate any help with getting started with the design of a knowledge base. (I am a novice, I got I believe some key things and I am continuing studying a FileMaker book.) The Knowledge Base: It is for internal use (not web publish). I want the KB to be both searchable and have some hierarchical structure. Each KB entry will have an ID (auto-enter), name, keywords, "tags" and body (main entry). The key…

I'm designing a simple fmp9 db for a farming project and would like input on its schema. The farm has 3 plots. They keep 3 input logs and 1 output log, and they keep track of weekly user hours per plot, with NO concern for which log the hours applied. no data needs to be summated across plots. At first I thought I'd have the following tables...plots, logs, users. But it turns out the 4 fields in all the logs are exactly the same (date, veggietype, variety, amt), so I wonder if the logs db is not necessary, and 'log type' should just be a field in the plot table. nothing will be summated across logs. I've considered having separate tables for input and output, a…

Hey everyone. I need help understanding how to create something like a journal within a filemaker database. i have a contact management layout, and within that i want a journal like field. the person using the database can select the date next to a note field, when he does that he can input whatever note about that day and it will save within that contacts information. is this possible? did that even make sense?!

How should I set a text or number flag field when any other field gets updated. Will the modification date will help me in doing that.

Hi, I'm new to filemaker and am currently trying to create a pupil assessment database for the school I work at. The database consists of a pupil information table and about four other tables that contain all of the other assessment data. This all works fine and dandy until I need to create a new record. Currently I have to go into each table add a new record and type in the pupil id. What I would really like to be able to do is create a new pupil record in the pupil information table and the other tables create a corresponding record automatically. I have been messing around with the 'allow creation of records' tick box in the 'edit relationships' dialogue box, but…

I'm new to this forum, so I'm not sure I'm in the right place, so forgive me. I own and publish a few small magazines. I'd like to either create or find a Filemaker database in which I can schedule advertising (name, size, run dates, etc.) and be able to print a report of which ads are to run in any given issue; for example, when I am about to begin work on the July issue, I'd like to be able to print a list of all the ads that will run in that issue and their pertinent information (size, color, etc.) so I can map out the magazine. I've been doing this manually until now. I know this can be done because we paid big bucks for a customized database program in the early…

I'm stumped! I have three files "Attendance" "Stats" and "Addresses". They are all related to one another and are listed in the "File/Define/File References..." box. They are also all listed in the relationship dialog. The "Attendance" file gets a date and associated stats from the "Stats" file. Records are created for that specified date in the "Attendance" file for specified names in the "Addresses" file. So Attendance and Addresses are related by a field called "Household Code" and Attendance and Stats are related by a field called "Sundays Date". My problem is that why everything looks good, it's not working right. Whenever I try to create records I get a dialog …

Hi, I'm getting back into Filemaker after about 10 years and trying to get my head around Tables and how they relate. I thought I had it figured out until I opened the 'Contact Management' template that comes with FMPro. It seems to do what I want but I wanted to reverse engineer it to understand it. The main table relates to 3 others called 'Similar Name', 'Similar Company' and 'Similars'. Confusingly though the three 'similar' tables to do not show up in my Table management window, only showing the one table called 'Contact Management'. How can I edit the related tables if they are not there? I hope this is clear.

I only have about 1000 contacts in my company's database, but am often having to move between records for information. Besides moving the little slider / button underneath the Records Icon—guessing where the Record I need is—are there more efficient, quicker ways to move quickly between records that are alphabetized?

I have a number of tables, each holding various important dates that we need to be reminded of. How do I have all these dates displayed together in a suitable reminder table format? Please point in the direction of what I need to read up on to solve this problem.

I think that what I am actually trying to do is to retain search criteria, but let me explain. I have a DB called PDB [plant database] and I use it as the source material about plants. It's a very husky and useful databank that I've developed over the course of 20 years or so. My workflow requires that I create conceptual plant lists as I move through a project [i am a landscape architect]. I find it most convenient to search the PDB for various criteria, generating a list that is used more as a touchstone during design than anything else. An example might be shrubs for a particular project that form a master list, excluding obvious plants that won't work and th…

I have a project that includes putting several offices in other cities on one central office server. Currently they run the same database separately. The issue becomes should they be combined into one database structure. I see some advantages along with disadvantages and concerned how this should be assessed.

Struggling Newbie:I am trying to move my Lotus Approach database to FMP8. My "Many" table has 4000 existing entries, and my "one", about 450. I have related the two tables and allegedly (at least followed the instructions) assigned "lookups" to two fields in the "many" table I wish to show in the "many" table taken from the "one" table. Nothing happens. The field in my layer form in the "many" table remains blank. The data was opened as a *.dbf and all else seems to be working well. The database is an art inventory; the one table is the artist bio. Now I link the tables via the Artist ID Number, which is a unique, automatically assigned number from the "one" table. I can'…

I am trying to set up the following situtation. If a field (First Name) is empty, then I want a salutation in a merge field to say "Dear Sir". But if the field (First Name) is not empty, I want it to say "Dear" (First Name) I have tried if empty clauses, but just cant get it to work.

Hi all, I am a complete novice when it comes to databases, however I was able to create a form and wanted to add fields such as supplier name, address, tel etc. from another table within the same database. I set the supplier name field that uses a drop down list to pick a supplier (successfully) and then wanted it to fill in the rest of the supplier details (address, telephone etc). I have created the main form and also created a secondary table/form with suppliers on. However I can't seem to get them to autofill (if that is the right terminology...any ideas? ....its really bugging me this issue - I can get it to look up a single field from another table,…

I have a sticky problem, at least it has been for me, and I am hoping someone will see the error of my ways and get me back on track. I have included a simplified Relationship graph that may help. I receive from another source two files (in Yellow) that represent detailed info of the members and a file of de-normalized file of assignments that include the team and district. During the import process I will split out the district and team info (in blue) to make this data more normalized. From the perspective of Team I want to include a portal that will list all of the teams in a specific district and by selecting on that district show all of the assignm…

I keep running around in circles on this one and I get close only to find I can't do what I want to. I hope someone can assist me fix what seems like a simple problem. I have related tables, one called Events, the other called Press. I believe it can be considered a many to many relationship since one Event can have many Press contacts and all Press contacts can be related to more than one Event. Thing is, I have only used it in one direction - namely one Event to many Press contacts. In the old days I used to use a silly relationship in FM6 that we won't even talk about, though all records in both files have always had Unique Auto-Entered IDs (whew!). I have no…

I have seen similar problems to this, but everyone has a slightly different flavour. I am writing my own Asset Equipment db. 3 tables so far: People, Equipment and Assignments, the latter associating people with their items and the table contains only PersID and EqID. What is tripping me up is that when viewing an item of Equipment I want to choose someone from a list of people and assign that item. Using a layout showing records from Assignments and passing a global ref to EqID does display the equipment details. I have a Value List from People displaying PID and Fullname (displaying only Second field) from which i wish to select the person to assig…

I have a customer table, and invoice table, an invoice items table (for the individual items purchased) and a products table. All works until I try to add a drop down to select the product as only one item is listed. Are are any examples around of how to set this type of relationship up?