Relationships

This is probably a classic type of example, but I haven't found the answer. How to create relationships for dependent tasks. By this, I mean that a task can have 0, 1, or many tasks that must be completed before it can be started, and it can have 0, 1, or many tasks that can be started after it (the current task) is completed. I start with a table 'Task', which has a primary key field, and a field for the task name. So far I've tried creating a join table with two fk's representing a prior_task and next_task, and relating the pk of Task to the prior_task fk of the join table, and the pk of the 2nd TO of Task to the next_task fk of the join table. Then I create record…

I want to make a portal that has field for a company code and a company name. When I type in the company code it must lookup the company name automatically , but when I lookup the company name (using a dropdown list), it should automaticly change the company code. (It should not change the company name assosiated with the code, but go to the record with the coresponding code) How do I go about doing this?

If a Master script step runs an external script (in another file) how do you return to and continue to run the Master script?

In the following example, I can easily get aggregate calculation results for the table occurence "DataReference2004", that indicate me stats about the records that match gSecteur... but I can't when I add a criteria in the relation that is based on a calculation field (GraphPosition30Parameter1) returning a number for each record and a global field (gCounter) in Viewer which contains integer numbers from 0 to 30 where GraphPosition30Parameter1 = Floor ( Abs ( (MinParameter1 - Parameter1))/(SpreadParameter1/30)) and is unstored, and where MinParameter1 is a calculation field = Min(Parameter1) Parameter1 is a calculation field = field1 - field2, wher…

Hi guys, I want have a portal with 5 random child records in it. Nothing to fancy, I've come up with a couple ideas on how to do it, but niether really give me that warm fuzzy feeling: 1. Sort the portal on a value list. The value list will a field with randon data in it. Then have the portal only show 5 records. Not sure if that will work nice or not. 2. Have a field in the child records have a random field and sort the portal on it. Again showing 5 records only. The problem is the random field needs to be generated on every record all the time. Anyone got a better way(s)?

Let's say you have a join-type table occurrence that can locate just the record set you're interested in. So you go to a Results Layout with a GTTR (from a Base Layout) and can step forward and backward though the found records. Now you want to use a button-based script to search just those records for some particular phrase in a text field. You want to stay on the same layout and view the constrained results and step forward and backward within the new subset. Then you want to revert to your original found set that your table occurrence provided. How can this be set up? Should the Results Layout be the basis for scripting the text search? -- or should you have …

I'm actually stumped on this one.... I have a database of "companies" I'd like the user to see a list of available "groups" as well as create and delete new groups, then next to it, I'd like to be able to "add the current company to the currently selected group" I'd like some feedback on this... upon writing this it seems simple, there are some other "relationships" in the file that may be causing me problems, but I'm trying to take one step at a time to making this work. Thanks in advance for any help!

We've used FM for over 10 years. We've recently moved from OS9 to Tiger, and with it from FM5 to FM8. We have a template we use to create files which contains lookup links to a master database. It worked brilliantly under FM5. But now when we create new files from this template, we lose the links. What are we doing wrong ?

Hello all, I've run into a problem and I cannot find what the problem is. I'm not sure whether this falls under 'relationship' or 'define fields', but the problems in this board looked more like mine. Any help you can provide is greatly appreciated... Scenario: (also see attached picture) I have a list of schedule blocks for an event in the Schedule_Database table. Each block has a day number, which is what day of the event it occurs on. When displaying data to users I want to show the actual date, so I have the Convention_Information table that holds this data. The Convention_Information table takes the date of the first day (that is entered manually…

I had a single table database with 19 fields and 26 records. I added a new table with 1 field and 6 records and selected 1 set of related fields. When I click OK and go back to the display, only the new table layout displays records information; the original layout records all say “table missing.” What am I doing wrong? Both tables appear in the tables window.

I need to calculate the minimum and maximum selling price for a subdivision. Here's how the data is structured: Each 'FloorPlan' is a record which has, among other fields, a selling price. There are multiple Floor Plan records. Each record also has the key of its parent record, the subdivision. So I have a relationship between each subdivision and all its related Floor Plans. I know I can script the finding of the min and max but that isn't a great technique. How can I do it in 1 or more calcs (using FM8 Advanced)? Steve

Hello, I'm a new member of the site and I've been delighted to see how many great tips and samples there are here for Filemaker users, so first things first -- thanks to all of the members who have taken out time to make these contributions and have made this place such a stellar resource for learners like myself. I'm making a database right now and I've hit the wall trying to figure out a way to implement one of the desired features, so I thought I'd run it by you all: There is a layout which is a detailed view of an individual record, consisting of the fields in the primary table of the database, and in that detailed view I have also put a portal which displa…

I have a table called addresses which has a field called current, each person can have a number of addresses; work, home, summer etc. I have a relationship to the address table called Current Address based on ID and the field Current, which allows the current address to be shown on all the layouts where it is required, it works well. Here is my problem, I would like to allow only one record to be set as current, but I can't figure out how to check to see if one is already set as current, and then message the user that another record is already set as current. I think a self-join could help, but not sure how to set it up. Any suggestions are appreciated. …

Greeting all - I am trying to create a scoring template that is based on serveal questions, and then a Yes or No radio button next to each question. A Yes scores 0 points and a No scores 1 point. Then on the next page(or via a 'get Results') button, one can see their cumulative score from all the questions. Make sense? First, can FMP do this? and second, any advice on how to start this? Thanks all.

My boss owns 9 companies. He wants me to create a mailing list database that will hold all the contacts in the same place, but seperate them by the business they are associated with at will. Any given Contact can be a customer of more than one business. So, it appears I have a many to many relationship here, right? I have tried to set it up a couple different ways, mainly trying to use a template, so far, to no avail. I'm a total newbie to Filemaker and also a pretty beginner database designer. I'm teaching myself..... Any ideas/pointers on how to set this up?

Please forgive the newbie. I'm a classroom teacher and not a hardcore FM user. I'd like to have a table of small actions for the classroom. "Write down a word," "make a drawing," "change one thing," etc. The details are not important. You could, if you like, think instead of the steps in ScriptMaker: "Go to Field," "Insert Text," etc. Each of these steps would be a record in the table. I'd then like to be able to combine those steps in a particular order into a sequence, just as you would arrange steps in ScriptMaker. The sequence gets a name and itself becomes a step. Then those steps can be combined with other steps to make more sequences, which can becom…

I am a little confused with many to many relationship / join tables. For example: I have a table with pet owner names; and a table with pet types. Each owner can have multiple pet types, and each pet type can have multiple owners. Both are listed with value lists. I would like to be able to choose either an owner and get all the pets they own, or choose the pet type and get a list of the owners. I tried doing this with checkboxes, but it wasn't working, and then with portals and it wasn't working. Can someone take a look at my example and give me a hint as to where I am going wrong? Thanks for your help! JP

Hey everyone, Sorry this is so long. I am a newbie and don't know how to explain it any better. Essentially, the main table I created is used to create and print quotes for a potential client (before they become a real client and get assigned a Purchase Order number). When I create a new record in the Main layout for a quote, I am able to select a client from a drop down menu that is tied to the Client table (which holds all client info). The two tables are linked by the clients name. I have also set up a third layout entitled Memo that is the printable layout for the quote. I created a script button on the Main layout to take me to the related record in t…

Let's say I have two tables: PRODUCTS and PRESENTATIONS, each with a unique ID key. Every product can be part of more than one presentation, and every presentation can include more than one product. So I create a join table titled PRODPRESJOIN. Each record in the join table contains 1 ProdID and 1 PresID, which are matched to their prospective tables. (See the attached example.) So, if I'm on a layout based on the PRESENTATIONS page, it's easy to create a portal that shows me all the products that are included in the presentation. But how do I create a portal that shows me all of the products that are NOT included in this presentation? In the attached example, t…

This is a dumb situation I caused myself, but wonder if there is an easy way out. I have two versions of a database which has one table that has gotten out of synch with some additional fields and some additional data. Since I have FMP8 advanced it allows me to import the entire table. I tried to do that and then reset the relationship to it, but the fields in the layouts seemed to require that I change each field one by one by hand to the new table. I can import the new data, but then I have to re-created the new fields by hand. I think there must be a better way to do this, but don't know what it is. Any suggestions are appreciated.

I'm a newbie to FileMaker but I'm enjoying the chalenge I've come up against the following problem: I'm building a application to generate quotes/orders and invoices. I'm progressing nicely with learning how to work the scripts, etc(thanks to this forum mostly) but I think I have made a mistake in my relationships. I have a company table and a contacts table and when I start a new order I can select the company and contact using drop downs. My problem is that any company can and does have more than one contact and I can't work out how to associate a contact with a particular company. I also want to be able to go from the contact page to the new…

Hi everyone... This is my first post to the forum, and although I am listed as a "beginner" in my profile, I'd prefer to call myself a "complete noob." I am working with the trial version of Filemaker 8 and am trying to put together a test database to help me learn the program.. I use Salesforce.com and see many parallels to FileMaker... I have spent quite a bit of time watching some tutorial demos online and feel like I have a general grasp enough to make something like a "DVD collection database" or something simple... I'd like to spend this week developing a recipe database that would allow me to generate a shopping list based on recipe choices and also base…

Howdy: I've been messing around with Filemaker, working on a project to track the sale of gift cards. My five tables (so far):/ Customers (customerId <-PK, customerFirstName, customerLastName, customerAddress1, customerAddress2, customerCity, customerState, customerZipCode, customerPhone, customerEmailAddress) Vendors (vendorId <-PK, vendorName, vendorAddress1, vendorAddress2, vendorCity, vendorState, vendorZipCode, vendorPhone, vendorEmailAddress) Cards (cardId <-PK, vendorId <-FK, amount, discount) Orders (orderId <-PK, date, customerId <-FK) OrderLineItems (orderLineItemId <-PK, orderId <-FK, cardId <-FK) …

I have hit a brick wall or have a brain freeze. I need to populate certain fields in one record with information from multiple records in a related database. The records are related by a single ID number. But... In multiple occurences of the record in the related database I only need information from records where a certain field containes a particular #. clear as mud?

Hi all, I'll try to be as succinct as possible. Lets say I have one table, Contacts, and 2 layouts, ContactsLayout, and CustomersLayout. At this point, both layouts are based on the contacts table occurrence. In the contacts table there is a YesNo field to say whether or not that contact is a customer. I want to display all the contacts on one layout (that's easy) and all the customers on the other layout (that's easy too with a find). My problem is this... I don't want to use a find to display the customers because if I am on a contact in the contacts layout and i switch to the customers layout, the find resets the pointer from the current record and sets…

Ok... a nice challenge here: 2 different issues in a pretty complicated database. I attach the file right away. Description: It is supposed to be used for entering financial states data: using the "Viewer" layout. The "codes" table define the elements that are entered manually or calculated. It contains a "Formule" field that determines how every code is calculated. When it is simply entered manually, the "Formule" only contains it's own code. The codes contained in "Formule" are duplicated in the "CalculationSteps" table, used as the reference for matching the data and the formula, in a Value list: "CalculatedReference". Using the "Result" calculation field i…

How do you set up a relationship between 2 database files?

Hi, I've just started to convert a v6 solution to v8, but taking full advantage of new features is still quite challenging for a v8 newbie. The solution has quite many tables, among them "Drivers" and "VehicleData". Drivers contains "static" truck driver information (DriverID etc.) VehicleData contains large amount of trip data collected from trucks. Each record contains DriverID, trip start/end dates,fuel consumption, speed etc. data from one trip. The solution has a feature for summarising driver-specific vehicle data from specified time range. For more refined comparisons, in the Driver table there is also three additional qualities, which may…

Hi all - I just want to pick your brains to see if anyone has any suggestions that I haven't thought of for a problem I am having in FileMaker Pro 6. I have a script that loops through a found set of records. For each record in the loop, I loop through a portal whose relationship is a simple ID to ID and set a field in the record to itself and the contents of a field in the current portal row. After looping through the portal I then print the a page that has the local field that contains the field data from the records in the portal. My client has been reporting that, the first time they run the script, the latest records from the portal do not show up i…

I would like to create a database to create budgets and print budgets for clients. I need to figure out how to set up categories, subcategories, sub-sub categories, etc. For instance, account # 2000 might be "Office" 2000.3 might be Copier supplies, and 2000.3.2 might be "Copier Paper", 2000.3.3 might be "Copier Ink", etc. Any ideas?

FileMaker can be a huge thrill or a big frustration. Right now, I'm a little frustrated. Please see the attached sample database. I need a royalty report that is specific to the songwriters earnings. If you press the red "Go To Royalty Report" on the songs layout, you can see that my two page report is displaying royalties as they relate to each song. However, I want it to go a step further - but similar in the way it looks. I want a report for each songwriter to show how much each songwriter has earned from each publisher that the songwriter is working with- (Yes, I realize that when it's all said and done, I will need calculation fields to figure out what percentage of…

Having adopted a bunch of zkf's in my life, I was wondering if there was a key command to jump to the bottom field in a table occurrence on the relationship diagram. If not, I can always start 'em with an underscore for now and chop it off later.

Let me start by saying that I haven't used FMP since version 5.5, so I appologize if my question has been answered by some new functionality. I'm trying to get used to the tables structure, so there very well could be an easy way to accomplish what I'm trying to do. I'm trying to figure out a way to explain what I need to do... this is not the application, but the theory is the same... If I have a table of invoices, each invoice has a customer name, a territory and a line item for each product. In another table, I need to create a portal that would show all of the products that a customer bought in a certain territory. If I create fields for the customer na…

Hello, I am redesign a database, and I would appreciate any advice on these relationships. I have started to connect the tables, but I have run into same issue I am redesign because of to begin with. As you can see in the attached screenshot, I have the "Stocks" & "Options" tables connected to the "Investments" table. I am attempting to use this "Investments" table to bridge the connection to the "Orders" table. I am the right track, or is there an easier way? Thanks, Bill

I have a contact manager db and I only want to display one address per person. Currently I have to run a script attached to a primary checkbox looping and setting any related address to non-primary. Is there a trick to doing this using a one-to-one relationship? Or another basic way without a script? Basically, if a person has three addresses and I set one to primary(1) all the others should be set to non-primary(0)

I am doing a image database & i want to use thumbnails layout which is available in Product Catalog template but once i changed the field names, the thumbnails layout dont work anymore - only image shown, info is shown as '?' instead of original info (text/number). What can i do to make my records shown in the thumbnail layout?

Have problem with a one of my relationships in my database. Have table for general computer information and one these fields is called “Computer Type” and is validated by using a value list with 3 options desktop, laptop and server. Below is the two tables and I have cut out most of fields. Computer Information Table ComID (Unquie Primary Key) ComName (Unquie) ComputerType Etc Server Maintence Table ComName TicketNo (Unquie) Etc The relationship is ComName on both tables and is one to many. What I am trying to do is only create records in the “Server Maintence Table” pulling out the information from the “Computer Information Table” whic…

Given a table with two columns, I'll call them "Current" and "Parent", which contain the names of a current node and that node's parent, is it possible to derive the full hierarchy for the current node on each record? The resulting hierarchy would appear in a third column on the table. I'll give an example with folder names (but the same logic should apply, I think, to topics/subtopics, chapters/sections, genealogical trees, org charts, etc.): GIVEN DATA (RECORD_NUM] CURRENT ; PARENT): 1] FolderA ; -nothing because it is the top-most node in the tree- 2] SubFolder1 ; FolderA 3] SubFolder2 ; SubFolder1 4] SubFolder3 ; SubFolder1 5] SubFolder4 ; SubFolder…

Hi guys, I have just got FMP 8.5 on trial for a company project. I am looking to track incoming collections and have set up a DB to help this process. What am I after OK i have 3 fields User ID, User name, User Phone: When i selct a user ID from the first drop down box(User ID) i want to auto populate the USer name and User phone? I have set up a seperate table with User ID, Name and Phone whic i have information in but i can not figure how to like them together. any help would be great. Thanks in advance Darren

Done a lot of database work for websites with MySQL, and just got acquainted with Filemaker. And everything seems suddenly much more complicated... Here's the aim. Made a "Productions" database with information on films. Got a "contacts" database elsewhere. One film can be directed by several directors. One director can work as director on many films, but also as editor on others, and so on. Here's an organisation. Created two other databases: - professions (director, editor, etc. with minimum wages and stuff) - contacts_productions (links a profession ID, a contact ID and a film ID) Now how can I allow users, in the "productions" layout, to c…

Hello, I have a very simple filemaker file in which I have a relationship between a fiscal amount in table 1 and a percentage field in table 2, which produces a result back in table 1. To make this clearer, my fiscal amount is say 500 my percentage is 1% therefore I expect to see a result of 5. This I have three times with the fiscali and percentage amounts being different for all three. To allow for this I have three tables for the percentage calculation. In my results field the calculation reads as amount paid * percentage table::percentage. For the first two fields this works fine, but for the third the result generated is always zero. This is doing my…

Hello.I,m very new to this and on my 1st d/b. I am trying to bring in to a portal situated in the main screen from an invoice table the invoice number and obviosly the customer name i have created a relationship ---customer situated in main screen and customers in the invoice table. Now comes the problem bit initially it seemed to work and brought in invoice numbers but they were all related to numbers long since deleted . I deleted all these via a short script but end result is that nothing now is brought in I have tried various combinations i.e invoice number to invoice number but to no avail I,m obviously doing something that is inherently wrong …

I have been looking at the filemaker template file resourse scheduling and managed to get the buttoms working through scripts. Now I only have to figure out how to make the information appear in the portal. Looking at the template a few things don't make sense to me: In the new project layout the fields you fill in are from "current table: Calendar" although the layout is called "new project" The portal in Resourse scheduling is showing the related records from Project Resourse ID? Nowhere in the define database can i find Project Resourse ID as a field nor is it as a layout. Although it is in the relationship window. What is this Project Resourse ID? And why d…

My file summarizes health insurance proposals for presentation to clients. It has two tables: one contains policy descriptions and rates (Table "A") and another showing the client's employee census (Table "B"). The census (Table "B") has fields for employee name, employee age, employee sex and coverage: Employee-Only / Employee & Spouse / Employee & Children / Family (from a drop-down list.) Table "B" also has summary fields that calculate the number of employees in each of these coverage classifications. Table "A" has rates for each policy that will be multiplied by the number of people in each coverage classification (from the summary fields …

This is an update to an earlier posting with a different subject heading -- but basically the same problem. I'm still going round in circles over how to set up related customer/inventory/lineitem/invoice files. I'm either missing some steps, doing steps out of order, or missing something altogether. My apologies to those who have answered my earlier posting: I'm just not getting something. I've looked through the forum but can't find any examples that explain the procedure in simple enough terms that I can follow. Let me see if I can break it down into simple steps and questions 1. once I get the four files set up and linked appropriately on mat…

Hi everyone. I'm hoping someobody can help me get over my first major hurdle so that I can continue getting to grips with this great looking package. I recently aquired Filemaker 8.5 and started playing around with it mainly for fun but also to see what it can do (and becuse I like learning new things). I bought some training guides and have been working my way through them but I have hit a wall and I know the answer must be staring at me in the face but I have spend days trying to work it out to no avail. Basically I have 3 tables (so far). The first table is the contacts table the second table is the Products table and the third table is a quotes table. …

Hi, may be just discussed but I'm not able to search it, apologise me. Here it goes. I do have a simple database file in which are stored the contacts of my company, among other fields there are their phone number in one field and their cel number in another field. On another databse file, I get the data from our PBX (central telephone). This PBX will generate a txt file with calls made from inside to outside. Now I want to build a relation, so that when the phone number in the PBX based will display, it will show the name of the person to which it belongs getting it from the first database. It's pretty simple so far. The problem comes because I want to relat…

My flat file could be improved greatly by converting it to relational. It contains about 300 fields that I would like to move from the main table to a new table. Is there a way to migrate fields from one table to another? Recreating all 300 fields in the second table would be very time consuming. Thanks!

I'm sure this sounds quite basic, but I can't get it to work: I have a solution with two tables. The second table lists counties and tax rates. Using the first table the user selects the county to use from a "Counties" value list that gets its information from the second table. Once the county is selected the appropriate tax rate is filled in on the first table, again getting the info from the second table. So far, so good. That part works OK. But when I try to place a portal for the counties and tax rates onto a layout in the first table, I only get the county that has been selected from the value list. Why can't I see all the counties and tax rates from the se…

I may have bitten off more than I can chew... I am creating a searchable database for curriculum in my school district. I have attached the file I am currently working on. What I need to do... Each PA Academic Standard needs to be linked to each individual Essential Question field and Content field. Right now I just have a large field to enter all of the information with out direct correlation to the individual ideas within each field (hope this makes sense...) Help. Any ideas? Master.zip

Most of the databases I have been working with, I have separated into a back-end for data and a front-end for layouts & scripts. I am wondering if anyone has any thoughts on how to properly setup the relationships. Should they all be in the back, the front, or both?

This may seem like a simple question, but to me it's mystifiing: I'm creating a DB solution that will display a list of counties and their appropriate sales tax rate. On one layout the user will enter all the counties he makes sales into along with each county's tax rate. I want these counties to be added to a value list so the user can select the appropriate county when making a sale on another layout. When the county is selected I'd like to have the appropriate tax rate for that county automatically compute the tax. I've added a second table to hold the counties and tax rates but that seems awkward because the user has to create a new record for each county. Is …

Hi, I have two files, A and B. In A I have a portal to B with a relation for a group of records (one group is 7 records, monday to sunday). In B I have a time count for each member of the group (day of the week). In addition to this I have a summary field looking like this... sum(self_relation::time) that sums up the times for the members in the group. But when I'm in file A with all the groupmembers listed in my portal, and I subtract or add time in one of the rows, the summary field does not update. If I change to show another group and then change back again, it recalculates. But not when I change any of the values that the summary field is based o…

Overview: We have standard Invoice to LineItems setup. Payments are applied against an invoice when the payment is made by adding a LineItem (Type = Payment) and the Invoice number is inserted. Credit Memos, Returns, NSF charges and other adjustments are also entered into LineItems and Invoice number is set to 0 (meaning yet unapplied). We can have multiple payments and adjustments against an individual Invoice. We can’t apply adjustments against an invoice until the payment comes in because we don’t know when the store will take the credit. When a credit is taken, we change the invoice number (in the LineItem credit item) from 0 to the number, thus applying it. …

My FM db schema is attached. Please refer to it and see if it makes sense. Specifically, does my relationship around Appointments, LineItem and Payment make sense? In addition, one of the things I would like to do is create invoices. I'm a little confused on whether or not I need a separate Invoice table for this?

Hi All, The general information: Someone requests a sample and I need to track who will be sending the sample, and who will be receiving it... The problem I can't figure out, is that the entity that sends and receives is the same: a Contact. How can a contact be 2 different things at the same time? Specifics: (looking at contacts.jpg) I have a Sample_Request table where I generate the request and setup the relationship through 2 portals into the green join table (Sample_To_Contacts) between the request and the contact. I kinda have it working but I know it's not the correct way to do it. (Notice I said 2 portals...looking into the same table with …

Hello All, I am looking for some guidance on the best way to set up a database... but first the background. I have created an electrical engineering component database for my company. The database contains fields like component parameters, data sheets, links to simulation files, links to PCB footprints etc. The database also has numerous fields that are used for "filemaker internal" functions like searching, scripting, and container fields for icons etc. My EE program (altium designer)uses ODBC to access the database, but unfortunatily, it isn't very smart about it. If you access a component in the database, it brings over all of that component's fields (including g…

Hi there. I'm pretty much a newbie with Filemaker. I've done a little work with it here and there, but all flat database types until now. I've been trying to learn how to develop relational dbs correctly, but I haven't wrapped my head around it quite yet. I've read a couple of books, and visit forums, and subscribed to Filemaker magazine, but I'm still not there. I need more hands on work. So I'm trying to develop a small db for experience. It's purpose is to keep track of the computer systems at work - their specs, location, etc. for a small company (about 10 systems). I've created two tables, a computer specs table and workstation table. The specs is just that, t…

Hello, I have a database made up of products. Each of these products has a unique ID number. Information on them also undergoes multiple revisions. I want to save these revisions. Right now all tables are related by the ID number alone, but I forsee that when a revision takes place, because of this it will change for all revisions with the same ID (which I don't want). Would the simplest way to keep everything sorted be to create a field that is common to all IDs with the same revision and use that as the relationship key? The thought in my head now is it would be a concatenation like IDNumber.RevisionNumber Sorry if the question sounds basic, I'm thinkin…

I'm still very new to FMP and just ran into a little snag when trying to figure out how to to information from multiple tables into a single line item. Taking a look at the red tables in my ERD: I want to create an Offering of some furniture products to a group of customers. This seemed to me like it would be a classic line item sort of deal. So I have one portal showing all the customers getting this offer and another portal showing all the products. But then I realized that each product can have many different finishes! At first I was thinking that I'd just have repetitive line items for each product and each one of their finishes but this is redundan…

Hello everyone, Ok, There's a little something I have not understood well. Here's the situation, hoping I describe it well: Note that I can not use variables in scripts (I think) as I am using version 7... so I use a lot of global fields... I have a global field somewhere, containing the date of a financial statement being currently edited (let's call it EFTable), which is copied, by calculation, in another global field of a table containing a list of edition fields (that can be called CodesTable). That last table is also linked to an occurence of a table containing the inserted values (let's call that one ValuesTable) The ValuesTable has a field conta…

Hi all - I hope this is the proper section to post in... I'm trying to come up with a calculation, but it would be based on related records. I'm having trouble figuring out how to build the relationship, though. Here's the background: I have a Parts table and a Trainings table. For each part, multiple persons can be trained. Each training can have a different status (from a value list), such as "Approved", "Disapproved", "First Time", "Update", etc. I already have one relationship set up (for another purpose) that links the two tables based on Part Number. (One-to-many from Parts to Trainings.) What I would like to do now is to define an auto-enter ca…

I'm enjoying the power and simplicity of "searching" a database using a few value-list checkbox fields, a multi-criteria relationship, and a portal in which to show results. It's much more satisfyig and immediate than FindMode / PerformFind for iteratively refining criteria. I don't know what people call this technique; I stumbled across it by accident. I'd like to learn more about it. The problem I'm having is in implementing the "any" criterion. Let me describe the database first: The "Meetings" table has records containing searchable fields Location, Day, Time, and Qualifiers. Each of these fields is value-listed; Location/Day/Time each take exactly one …

hi, Here is attached file where I have trouble in reationship. I am geting a message that one or more between Relationship these tables are invalid. Any kinf of help would be apreciated. filteringValuelist.zip

First of all, i'm not sure if this is the correct forum, my question doesn't seem to fit any of the forums but this looked like the closest one. I'm having a problem with data entry in a related field. Here's the scenario: The first table "AudioCD" has the foreign key _ArtistID Another table "Artist" has the primary key __ID and stores the field "name" The two tables have a one to many relationship. I have allowed the creation of related record in the Artist table via the relationship For data entry I have a layout called "Audio CD" based on the AudioCD table. In order to enter the artist's name i am using a drop-down list associated with the _Art…

I am trying convert a database from a multifile FM6 to a single file FM8. This is used in a multiuser network hosted on FM Server. (I am currently changing it off network) I have a relationship that is not working. On one side I have a global field that I am trying to set as the Username (as defined in preferences). In the previous version it was set via a startup script and I am trying to so again. On the other side is a calculation that results in a list of names. I have several portals that show lists of records they work on for each person using the database. Now the problem... I can set the global field just fine. I originally had used a s…

Hello everyone, I read a lot of topics but didn't find the solution to my problem. I'm about to build an inventory database. I'm using one table in wich I'm adding records. Each record has - A category Name - A category Number (wich must be calculated regarding name) - A sub-category Name - A sub-category Number (wich must be calculated regarding name and unique within each category) - A product number wich must be unique within cat and sub-cat If two records have the same category name they must have the same category number. Same within a category with sub-categories. The solution I want to find is quite near serialisation by category (u…

unfortunately, i have fp 6.0 and this is the closest topic to my question: i want to create (i think) relational databases that are associated with each other with this situation: i want to enter a code (call it 'code 1') in a field. when i do that, a second code will be associated with it (call it 'code 2'). another field is a description of code 2. i also want it to work visa versa (look up description, and code 2 comes up). i have 300 code 1's that is associated with 100 code 2's. each code 2 has a description. i guess it's the same thing as someone looking up your telephone number (you also have 2 other numbers), and your address and zip code…

I am working with two databases. In one database (A), I have a product field and a corresponding price field. In the other database (, I have a product field that creates a drop down box of products based on the vendor info from database A. How do I relate the price to the product in database A and how do I have the price automatically populate in database B based on which product I select from the drop-down box.

This relationship was used (by Soren) in a sample file yesterday, but I have not been able to find any reference to its type or recommended uses in the FMP documentation. Can someone describe it?

I am having trouble with a relationship in my FM Db. I have a car detailing business with multiple locations. Each location has it's own set of available services. It is also possible for an appointment to have multiple services. My problem is with the relationship between location and services. I have attached a screenshot of my schema. I want two drop-down list controls on the Appointment layout. The first is for location which will then dynamically generate the second drop-down which should be the list of available services for that location. I understand the basic concept of how to implement this within FM, but my current schema is not setup correctly (as…

Hello! I´m doing a Music (cd) Catalog and i would like to know how can i see the album tracks in a list. I would search for an album and then i would like to see the tracks that are related to that album... I have something like the previous post Dj catalog. Thanks

I have a table called Call History. I want to have 2 date fields in the header of my layout called Start Date and End Date that will filter the records that show in list mode on my layout to records between these dates. How do I do this? TIA

I'm doing a side project for an ex-co-worker. He's an undergrad advisor and wants to replace a bunch of paper-based degree-requirement checklists with a database. There are eight different checklists (for various combinations of major, minor, joint-major, 2nd degree). They're most interested in getting just one (the standard major checklist) automated, but I'd like to make this as extensible as possible. I don't have a PDF of the checklist (I'll scan it at work tomorrow if anyone wants to see it), but it looks something like this and should be pretty familiar for anyone who's been in a college or university: Lower Division: ------------------------------…

Hi, I am in a problem may be some one can give me the solution, I am running a college, and I am using file maker for the student record as well as for preparing letters for students. There are five databases I am using now: Student record Acceptance Letter Enrolment letter accomodation letter Support letter What I want to do is if I enter one student data in Student record file and gave it a unique refernce number, and then i open the letter databases one by one and enter that unique student reference number then the student name and his address will appear automatically. If some one knows the answer please let me know. Regards

I have three files: Donors, Hospitals and Technicians. In Donor file I have two fields: RecoverSiteID and ReferralSiteID both are related to the Hospital file and work correctly. Now I add two other fields, EnucTechId and PreservTechId, to Donor to be related to Technicians in a similar way. Both relationships do not work. WHen I view the Relationships the leftside of the the relationship is assigned to the Technician file and the right side is the Donor, even though I entered them the reverse way. These seem to be the only differences from the other relationships that work correctly. Can someone please tell me what I might be missing?

Hello. I need help with a small problem. Among a lot of fields, I have the field "cust_nr" and the field "cust_name". They are both located in the Table "TrspReg". I have set both "cust_nr" and "cust_name" to work in a drop-down list. Now, here is my problem. I want to be able to i.e. enter the customer id number in the field "cust_nr", and then the customer name shows in "cust_name" (this I have mede work), But I also would be able to enter the customer name instead of the customer number. I.e. I enter the customer name in "cust_name" and the customer number shows in "cust_nr". That I cant meke work... I need to make each field be replaces by th…

Hey I'm new to these boards, and I'm here looking for some advice on a DJ Catalogue I'd like to create using FileMaker Pro 7. I've got a decent-sized DJ business currently, and I'd like to be able to bring my laptop along to gigs and have a searchable FileMaker database of all our songs. This would help by showing us the exact track and/or disc of the requested song. I read most of Jonathan Stars' "Learn FileMaker Pro 7" book, which has given me a pretty good understanding of general use of FM. I'm also very experienced with PHP and MySQL, which helps. My main question is one on the design of the database. Here's the problem I'm facing: Our DJ catal…

Hi, I'm trying to come up with a design for a single list view of identical data from three distinctly different source files. That is, I want to create a single list view in a separate FM file that presents a list of contacts from a workgroup FM data file, a department FM data file, and a Corporate FM data file. The structures of all of the data files are identical. The point is to give the user a seamless single list of contacts (thus the data from the 3 FM files are intermingled in the list view) that can be sorted/manipulated in various ways. I'm trying to come up with a way that doesn't involve imports. Thanks for the help...

This question may be identical to caman's post (directly below mine), but I'm not sure I understood his correctly. I have a 2-table database. Table 1 is "People". Fields are: last_name, first_name, email, etc. Table 2 is "Contacts". Fields are: Date, last_name, first_name, email, decription, etc. Every time I contact one of my "people" it gets logged with a new contact record. I've formatted my contact entry layout so that both the last_name field and the email field are dropdown lists. I know that I can set Contacts::last_name to be autofilled in when I enter Contacts::email. I also know that I can set Contacts::email to be autofilled in w…

Has anyone work on a DB that is 'Session' based. I have multiple users that need to keep their data separate and secure from other users. (e.g. with a Student DB, a teacher should only be able to view their students infomation) What is the best way to approach this. I been thinking a session table, and accessing data via portals but they will loose the find feature of filemaker and I will have to create something for finds. The other option is to have them work directly in the table, with me locking down standard navigation and forcing the find script to add their account key to every find request. hope that is clear, and if you have any suggestion I would…

Relative newbie needs help. I have attached a jpg of a FM form I designed and am using to display product "specials". Each box on the form represents part number(s) that I sell. Currently I am manually entering the price into the jpg when I create it..so it's actually part of the jpg. What I would like to do is to use the related database that contains the p/n and pricing data to display the part pricing for each of the p/n's displayed in the jpg's. Is that possible? ..or am I just brain dead over here?

Hi, I've been trying for a few days to resolve a problem i'm having with my fm8 database. Basically, I don't know how to relate the staff available days for working at their preferred site to the site days that need to be staffed. I would gratefully appreciate some help with this one. I've got to try and get this finished for monday morning. I've attached a pic to hopefully clear things up. Many thanks in advance guys and gals!

I'm working on a database that came into my possesion about three weeks ago. It has passed through many hands, has been copied a few times, backed up a few more times and some of the file referencing is a little off. The main database references three other databases to fill in fields, some with images, some with information. However, the current, up to date database that I am working in is referencing old databases. . . and I want it to reference the newer ones. I am struggling to change the file referencing, and I think it is because somehow the file extensions were lost or deleted in the process of upgrading to newer software or saving backups. From what I know, I…

Hey everyone, Over the past few months I have taught myself FMP from scratch in order to create a database entry system for the new company I am working for. We sell custom window treatments and are looking for a way to migrate from our antiquated paper based system into the more efficient world of the computer. Here is my problem (and I have attached the graph to make it easier to follow) Each individual order that gets processed may have more than one supplier providing the materials for the job. To track this possibility I set up the Manufacturer Orders table to be linked to the Contract table which handles the orders themselves. The Manufacturer Order …

Hi in an earler post I had problems with my relationship table. I have on the whole got a better looking and working relationship table but I am having a lot trouble with my "job intruction layout", in this layout I have items and amendments that only need relate to one "job instruction ID" as you can see with the attached file they are not showing. does any one have any ideas, I think it still to do with my relationship table not quite working right please see attached FHpro file Cheers Damian print_specs_Clone-v5.fp7.zip

plz anyone help me how to make value unchange between relationship .

Hey all. I'm not real familiar with relationships and I'm having an issue that I'm not sure how to fix. First - I have a table for Country/Country Code so the end user can enter Countries (to populate drop down field in other tables) to their Country calling codes Second - I have a table for Supplier Information. On the entry screen for Supplier Information the end user selects the Country from a drop down and the Country Code is auto filled Third - I have a table for Contact Information. Idealy I would like the entry screen for the Contact Information to work the same way where a user selects the Country from a drop down and the Country Code is auto f…

Hi I have been away from the forum for some time, but have got a little further with filemaker, I am having some real trouble trying to work relationships. I am trying to navigate around my job tracking database for a one person graphic design firm. I am having trouble getting from my project layout to other layouts. I have the relationship working on the whole across the database but it seems to be working badly. When I try to create a script button to link from a portal table to the related data on the portal this doesn't work. I have managed to do it in other parts of the database but when it comes to linking out and from the project layout I am having grea…

Hi, I'm just getting to grips with FileMaker and I'm having a problem updating a field value in another table. I have an ID No field in my 'customer' table and an ID No field in my 'contact history' so that I can relate the records. I've used New Records, Set Field and Commit in my script, but the value will not copy over into the 'contact history' table. Any ideas?

im currently testing solutions for a larger database im going to create in the near future, and ive run into some problems... its thought to work like this: a user on a website logs his name and a product he is interested in one the web, these 2 "id"s are then sent to the database as "people_id" and "product_id". they are both numeric, as of right now im just entering these by hand... next to the id_s i want the name to pop up. thats the problem thought. depending on how i connect the relations i get either the people_name or the product_name, i need both. what easy part have i missed now? atleased im trying not to bother myself with repeating ???

Hi I'm having real problems resolving a problem with my relationships. I have the following tables set up STAFF Key: StaffID (linked to A and B ) STAFF AVAILABILITY xStaffID (A) DateAvailable SITES SiteID (linked to C) PREFERRED SITES xStaffID ( xSiteID © SITE DAYS SiteDayID F keys? SELECTED SITE DAY xSiteDayID Basically, what i'm trying to do is...the dates for a particular site appear in a selection portal (based on Site Days). As the user clicks on the date in the portal, another portal populates with a list of staff available on that day to work at that site. Then the user clicks on the staff name …

I have two tables that are related using multiple fields. Those fields are the ID (primary and foreign) and a name field. What I am trying to acheive is the ability to enter a global search field, and have the results in the portal show names relating to what is entered in the global search field. I have this all working fine. What I cannot figure out, is how to have the portal show all records, and THEN filter based on the global input. Once vaules are removed from the global field, all records in the portal should show again. I am using FMP Advanced 8 and OSX 10.4. Thanks in advance.

I feel that this should be something I can do, but I am really having a tough time! I've created a set of related tables... STUDIOS<< MOVIES(acts as a join)>> ACTORS So when I look at studios, I can see all the movies a particular studio has produced. In addition, I would like to see, per studio, the number of films that a studio made that had a female actor in the lead role. I am hoping that this is an easy problem, and I'll say "oh yeah RIGHT! now I get it" but I've kind of given up trying to solve it on my own.

Hello FileMakers! *big breath* ...Alright, I've got this database halfway there (sorry it's in such a mess), but I have two questions. Firstly, I feel I have far too many different layouts, but I don't know what I should do to trim them down. Most of the ones I feel are extra could, I suppose, be condensed into value lists, as all they do is power them, but something's kept me from doing that because I'm scared some behaviour will change. I think value lists are probably the way to go, but some assurance would be more than welcomed. Secondly, I realize it was already sort of started here (so is my understanding) http://fmforums.com/forum/showtopic.php?tid/17781…

Hi dear helper! I am trying to make a payment follow up system for a school. In my database I have the following tables: Students (contains all records of students divided in their class). Payments (contains all payments related on student and class). Costs (contains all costs based on class, this table is related to payments with class). What do I need? A system that tells which costs are to be paid by the student (for every class). These costs should be in the costs table, and automatically created for each student I think.. What else, which costs he has already paid, and most important which costs are still to be paid. Can somebody poin…

Let me preface everything with: I'M A BEGINNER!!! Ok, now that we have that firmly in place.... I have the following related tables: CONTACTS JOBS (as in separate repair tickets) Each CONTACT can have multiple JOBS. These two tables are working fine. When I look at my CONTACTS, I can see that person's jobs, etc. Now what I have been trying to figure out is how to get a portal to automatically show ALL of my Contacts in alphabetical order on a separate MAINPAGE layout that will hopefully, eventually also contain the following info in separate portals: 1. ALL CONTACTS (as already mentioned) 2. TODAY'S JOBS (currently underway) 3. PENDING JOBS (po…

Hello, I was wondering if someone can help me with this. i created a relationship between Databases, one is call Student Files and second one is call Event Schedule. Basically student are assign to events being input at the Event Schedule, which get display at Student Files DB. I am running Mac os X Server with FM 5.5v4. This used to work fine until the Event Schedule started having some issue where it wouldn't let anyone add schedule to it. I did check on the DB's,there was some problems. I end up doing a recover of it. It is working now but it seen the relationship between both of them got broken. Now when someone try to add students to the events sc…

Only one relationship can occur between table occurrences. FileMaker asks me to create a new TO when I create a relationship that violates the rule I just stated. I have a database in development that has TOs connected something like this: A-B-C-D-A (some of the TOs are join tables). I'm able to create this in the graph, but during troubleshooting it seems like (at least) some of the TOs are in effect related twice, due to the chain (sort of circular, if you know what I mean) effect. The reason I state that, is that some relationships are not working, until I break the chain with a new TO (more hub and spoke-like). Can anyone comment on this? Or is it entirely dependent o…

Hi, I'm having a problem with getting a correct sort via two tables using the sort functionality in the edit relationship window. I'm working with Filemaker Pro Advanced V8.0.3 on an PC running XP home. I have three tables A, B and C. I need information visable in table A from table C. Table A has a relationship to table B and B has a relationship to table C, so displaying a field from table C in a table A layout is not a problem. Where I have a problem is with the sort between table B and C. Table A contains telephone calls made by customers. Each call has a Customer ID against it. This ID is the relationship field that links table A to table B. Table…