Relationships

Hi, I am fairly new to FileMaker and am programing a to-do type database. I am trying to figure out how to set up the relationships between my tasks and when I do the tasks. Here is my problem. I have a table with tasks which includes taskID and other specific task data. I want to be able to link a task to a day, but want the option of setting the time or not. It sounds easy, but the catch is that I want the specific worktimes to be pre-defined (I set the times on specific days that I want to work and then assign the task to those worktimes). If I have more tasks then I can accomplish in my worktimes, the extra tasks will be assigned to a day generically (without…

Hello, and thanks in advance for any help you can provide. I am making a database that keeps track of alumni who have started a company. Alumni can start multiple companies, and companies can be started by multiple alumni. I have made 3 table. One for companies, one for alumni, and a join table that relates the two. Each person and company has their own unique ID. In my setup, I have a portal that brings in the companies that have been started by the person. My problem is that it is still possible for "Person 1" to have multiple entries of "Company 1." Meaning that my Join table is currently allowing multiple records that have the same Company ID AND Contact…

Hi., I've hit a wall and am getting lost. A tip in the right direction is appreciated. I have following set up: Tables: Products Invoice_Line_Items Invoices Customers They are related as: Products to Invoice_Line_Items via Item_Code = Item_Number Invoice_Line_Items to Invoices via PO = PO Invoices to Customers via Customer_ID = Customer_ID All is in order and everything works perfectly. I would like to add one more table called Financial_Info that would hold Customers bank and payment information. So basically once I create a new customer a record in Financial_Info would be created automatically and would pull the name, i…

I hope this is not considered a double post but here goes. First off, thank you all again for the help you have given me as I learn Fiilemaker. It is greatly appreciated. I am still amazed at the level of expertise and willingness to share that exists in this community. Now for the problem. I have designed a solution that I use to manage a number of condominium projects. With this solution I bill the units for utilities, maid service, and other miscellaneous fees. This was working quite well. When I first designed it, I did not take into consideration that the units would one day be sold to other owners or that some owners would use their units as rental …

Hello there, I'm not sure it is actually a bug, but I've found that When you define a relationship based on "≠" or "<>" FileMaker 9 behaves differently from 8.5 An empty field will no longer be considered different from a field with a content. It means that you always need some content in a field to trigger the relationship. Can anyone else duplicate this= It it a bug or just a change in the engine?

Setting up a yacht race scoring application. 3 tables Boats - Boat ID, Boat name, handycap and other boat info. Races - Race ID, Race name, start time, distance and other race info. Timeslips - Timeslip ID, Boat ID, Race ID, finish time, corrected time and more.. So, the timeslip links the boat's to the races and gives the finish time nessisary to calculate the corrected time. Sorting a timeslip porthole on a race layout by corrected time gives a list of boats in order. (IE the first one is the winner) A running summery field gives a nice line number as each boat's place. This works fine when scoring a race. But when looking at a porhole of time…

My Unit uses a filemaker database created by my predecessor. We have a contacts file which contains a calculation field which gathers fields for Firstame Surname & address and presents them as an address label. Our Job Daybook still has us inputting these details manually. How can I get a link so that a PIN number can be entered in Daybook and the calculated address appears on the Daybook screen, I would expect staff to begin to remember PINs of regular clients, enter PIN and see adress appear - for those not so regular or new clients, check the contacts file for the PIN to enter... Is this readily achievable for a non-programmer? I do have a few…

Hi, I am trying to calculate the total cost (sum) of the same items from a drop-down list. For example, my drop-down list contains several items for invoice purposes: (1) Salaries (2) Contracts (3) Services Each item can have a varying price. Example: In one invoice, (1) Salary may be $3000 In another invoice, (1) Salary may be $4000 I would like to create a calculation that says, "Across all invoices associated with a particular budget number I would like to know the total costs associated with that particular drop-down selection [(01) Salary], for example. I have provided 2 images: One shows the layout and the other shows the field na…

I've been looking at this for some time and can't really see an elegant way to do what I need. I'm essentially dealing with 2 tables, Personnel and Timesheet. What I need is to run a payroll report that will show me how many hours each person in the Personnel table has entered in their records in the Timesheet table (with sub-summary by date). I have previously done this by looking up all Timesheet records in a date range (date range submitted via script to a global field) and then created a layout that summarizes by person and date. The problem I'm trying to work around is if a person has not entered their time in the Timesheet table - they don't appear on the…

Hi, I have created the tables for my small database (15 tables) . For my many to many tables I have created the join tables but I'm not sure about creating the compond keys (since every tables has to be identified uniquely by the 2 foreign keys from the parent tables. Any suggestions? Thanks in advance ;)

Hi. GenX - you mention 2 new table occurences, and in the relationship screen they are red. What does this red mean and how can I set it up? I have copied all those entries across so far yet this is the only discrepancy. Greg

I ran a few basic searches and skimmed this board, but could not really find anything that helped (though I may not even know for sure what I am looking for I have always heard/read 'never use repeating fields!' so I am unfamiliar with them (and it has been awhile since I have done any developing...). Anyhow! Here is my scenario: I have a single table which we are using to build a list of terms/definitions for a Glossary. I want to be able to display 'related' records. I feel like there must be a very easy way to accomplish this and my head is just trying to make it way to difficult... Currently I ran a relationship between the ID and a new …

I have two tables.. one's a staff directory, the other keeps track of absences. The staff happen to be teachers, and some teach at multiple schools in the district, so they may have multiple records in the directory.. one for each school (since school is a field). The two tables are related by ID numbers. On the absence table, I'd like to be able to enter an ID number, and have the rest of the info (name, position, school) automatically pull in from the directory table. The trouble is, when an ID is entered, it will find the first record, which may not have the correct school. Is there a way to select the school on the absence table, or should I create a unique ID f…

Okay, so I've got the four databases to make up my invoices. I even get the portal to work to list the invoice history for each customer. My question is how do I get a display of the item history for each customer. So if, over time and several invoices, the customer has bought various items, I want it to list all the items and hopefully the quantity. Ex: Cereal 5 Cheese 3 Eggs (carton) 8 Milk 4 I'm using FMPro 6 Unlimited. I'm also trying to get the customer to see this result via their web browser. invoicedb.zip

Hello, I'm new to this forum and no expert in FileMaker, I hope that someone will be able to help me. After quenching my brain for almost two weeks, I'm about to finish (%85) developing a little financial software for where I work. I've encounter some problems and I'm not sure it's a relationship or calculation issue(therefore I posted it on both); my database has several related databses files the main ones are called "[color:orange]Core", "[color:orange]Budget", "[color:orange]Code 1", "[color:orange]Code 2", "[color:orange]Code 3". The main fields in each file are: [color:orange]Core [color:green]Code 1 Set, Code 2 Set [color:orange]Budget: [col…

As a novice I spent hours building a DB for a convenience store, in my zeal to see if it worked I entered over 1200 Product ID's, it did work initially but my hopes were quickly dashed when I realized it was too cumbersome to search through over 1200 items when I was creating a purchase invoice. The purpose of the DB is too make the year end inventory process less of a nightmare, I wanted to enter purchase invoices inorder to quickly look up wholesale costs at year end so that I could valuate the year end inventory on hand. I often purchase the same product from several different wholesalers (depending on the cost), some of these wholesalers provide their own p…

I have media databases that I am not sure how to merge and am afraid of messing of the relationship structure: Table 1 = list of tapes with portal to table 2 Table 2 = contents of tapes Table 3 = table made from Final Cut Pro columns (we add to this one by importing batch lists made from Final Cut Pro). Each row may have the same tape, but different description of the content at that point in the tape. Tables 1 and 2 = 1 to many relationship by unique id How do I get one instance of a tape name from Table 3 into Table 1 and get all of the contents for each tape from Table 3 into Table 2? The way this database is supposed to work is that for each ta…

Hummm, doing a criteria ADD to a relationship in FMP 8.5 for portal filtering is so easy. How does one go about achieving this in relationships using FMP 6 ? :o

I am building my first relationship style database ( usually go do a single table) and I am a little confused about how it all comes together. I have 4 seperate tables that i have linked by a key field in the "Main table. I have these set up to create new records in the "Main" table as I import new records. However The "Main" table currently has 0 records in it. The 4 other files have (128, 584, 9668, 40,875 records Total 51,255) How do i make all of these records show in one layout with access to all fields from all the other table? And how do I ensure that this will continue everytime I upload new records into these 4 tables. I'm not fa…

I hope someone wants to dive in and help me with this, as I'm having trouble getting the design worked out in my head. The scenario: A manufacturer uses various different items to create a finished article. I need to be able to track running totals of all the different items that are used. This is straight forward enough. However, jobs are first quoted then later confirmed, at which point production begins. Once the job is completed the actual amount of each product used needs to be recorded (as it may not match exactly with the amount quoted). I need to flag if items: - are out of stock - are below a minimum reorder level - will be out of sto…

Hi: First time post. Just upgraded from FM 5 to FM 8.5. Bit of a learning curve, but not too bad, struggling with this though: I've attached a jpg labeled "my_problem.jpg" for reference. "Evaluate this calculation from the context of: ." Clicking on the drop down reveals an empty (blank) list - no tables are available. This file, named Media, was created in FM5 and converted to FM8 along with 2 other databases named Client List and Master Media List. See the screenshot of relationships. All my calculations in Media are showing "from , = Count (etc..)", Although unfamiliar with tables, I'm pretty sure I should be able to see the media table in that dropdo…

Hi, I've attached pictures from the progress in my portal. I've been having problems since FM 8. Whenever I create a new record in the portal, it sort of commits and creates another record instead of staying in the same record. It's really hard to explain, but if you look at the pictures I've attached, you'll see what I mean. I hope someone can clarify why this is happening please? Thanks Robin

For the life of me I don't understand why it does not work I am redesigning my invoice database that originally was done in FM 6, but now making it in 8.5 Before, I had three files: Invoices, Fees, Invoice Items. In order to avoid adding duplicated invoice items to Invoice, I had a calculation as a unique ct_invoiceID_feeID_key ( invoice_id & "-" fees_id) set up in Invoice Line Items file. Upon adding a new invoice items, the script was setting up a global field in Invoice Item file with that unique concatenated key of a new invoice items I am about to add, and then invoking a script in Invoice Items files to check this global key fee against keys of …

Hi, I'm a newbie to FileMaker. I have a product database that has duplicate records of the same product - lets 123A, 123B, 123C for example. Each product has a price field. I would like to update only the 123A price field and have the 123B and 123C price fields update automatically. Any ideas? thanks.

Hi there - I'm totally new to FileMaker, so bare with me if I keep asking questions here. Let's say, I have 10 pieces of Product A to test if it's good/bad and I want to be able to know each individual pieces if they are good/bad. See the attachment, to see what I'm talking about. The question is what would be a good way to see each piece's status? Second, if you go to the report layouts (again, see attachment). I can only see ProductA, but not ProductB. I know this involves with setting up relations, but I have no grasp of concept in relations in FileMaker at all. Can somebody touch up on this? Send me a file or some links that might help me with this type…

I'm trying to link up two tables with ids without the user ever seeing an id. I have two tables: PRODUCT product_id - number - Auto-enter Serial, Unique product_name - text vender_id - number vender_name - calculation - Unstored, =contact::contact_name CONTACT contact_id - number - Auto-enter Serial, Unique contact_name - text I have a relationship defined as follows: vender_id = contact_id The layout for the product has the name and the vender name (vender_id). The vender name is a dropdown list that uses a value list showing two different fields (contact_id and contact_name from contact table). The vender field will show the list of nam…

As the Administrator of an alcohol and drug counselling centre, I have been asked to develop a client database which contains "Client record" = general client info. "Case file" = This will be accessible via the client record and act almost like an invoice header. "Session lines" = This will relate to sessions within a case and will act almost like the cost lines of an invoice. This system will be used by about 10 people, will it pay to make separate tables for each worker for this own "Client records"? Confidentiality is a must for this type of work. I'm guessing if I create seperate tables for each workers "Client records" I can create accounts which will a…

Hi all! I know that this kind of question has been asked before, but I'm sorry if I've missed an obvious thread somewhere in this forum... My client wants to create an Invoice based on a Quote, and then be able to make changes to the Invoice without affecting the original Quote. He also wants the Invoice's unique ID to reflect the Quote's, so that when Quote_ID is "Qu001", Invoice_ID becomes "In001". Any ultimate solutions floating around out there?! Two separate Tables or not?! Any ideas/suggestions highly appreciated regards Anders

Hello: Does anyone know if there are any bugs with the GTRR command in version 8 advanced of FM for the MAC. I'm using FM Pro Advanced 8.0 v1 and I seem to be having an issued with retrieving the right set after using the GTRR command. Dom

Can a relationship be based on a calculated field? I'm trying to use a relationship like a find and use it in a calculated field to give me a total. Specifically I'm trying to get a total amount of a product from a list of orders after a date, the date is a calc field. So far the relationship is not working, but I just want to be sure if it is even possible.

What I need is to be able to enter two fields from dropdown lists, and have the third field fill in automatically. The closest I can come to a solution is via lookup. But it doesn't. : I suspect relationship trouble. What am I doing wrongly? All replies humbly appreciated! LookupSchmookup.zip

I have two tables table1=FVR table2=POC. There will be three contacts on each record in table1. table2 is a list of those contacts. I want to type in the last name in table1 and have it pull the record from table2 and fill in the rest of the info. I figured out how to do it with one contact, but not with multiple.

In simple terms I have a To Do file that I want to use to view all the completed items on one layout. I would like to view the "To do" items on another layout. I though I could do this without scripting but, so far I have not got it to work. Can someone help me set this up.

Hi all, I'm totally new to this, so bear with me. Here is what I need to do: When I create an invoice with parts from my inventory, I need the inventory to update the quanity to reflect that. I have all the data in place, I just need to figure out the relationship between my quanity on the invoice and the quanity in my inventory. Any help?

4-03-06 02:10 PM - Post#200710 In response to alphanitrate Hi alphanitrate, You would use an Auto-Enter on Field 1 (be sure to UNCHECK 'Do Not Replace Existing Value' with: If ( Category = 200 ; "yourCheckboxValue" ) ... where yourCheckboxValue is whatever is specified in that checkbox value list. But does that Value List contain other values as well? If so, change the calc to: If ( Category = 200 ; "yourCheckboxValue" ; Field 1 ) It helps to have specific examples. The first example assumes only one value - it becomes a toggle and will only allow a one value or blank. The second example allows multiple values. LaRetta Hi alpha…

I'm sure this is an easy one... I just can't wrap my head around it: I have a portal to track the value history of a piece of artwork. Artwork being the Base Table, linked via record id to the table containing all the values entered over time. Simple. How can I get the most recent value (date = key-field) to flow into a field in the Base Table? The idea is to be able to display the value or the artwork in the list view of the Base Table. Can anyone help? Thank you so much! Michael

I am working on a solution and am trying to create it using the Anchor/Buoy method. The solution is for a tour operator. Clients sign up for a tour over the internet. I have the following tables: Reservation (the "Anchor" or "Squid Head"), Pick Up Location, Van, and Tour. Most of the information is viewed from the Tour layout via a portal. This may also be helpful: The match field between Reservation and Tour is a date field. My question is how do I relate the drivers? It seems like it could be a many-to-many relationship, so I could do something like Van to Date (join table) to Driver. But are many-to-many relationships part of a Anchor/Buoy or Squid method? …

I have 3 tables. table1=schedule, table2=lookup, table3=job specs. I need information copied from table1 to table3 and remain on table3 even when record is from table1 is deleted which is why i have a lookup table between the 1&3. My problem is when I enter a new record in table1 i cant seem to get that information to show up on table3. Are my relationships between the tables wrong? Please help, file attached. -Rob Hunt jobspecs_copy_1.fp7.zip

I have a relational database with three tables. After I create a new record in the parent table [Artists], I want to create a new item associated with this record in the Prints table. How do I copy the ID value in the parent table and transfer it to the new record in the prints table? In the Artist information layout, I have a script attached to a button that is called Add New Print that does the following: Got to Layout (Prints) New Record Set Field [Prints:Artist_ID; Artists::ID] It creates the new record but does not change the Prints:Artist_ID, therefore the two are not connected. Should I be using a look up value instead of a script that does th…

Hi, I have 2 categories of clients - “banks” and “others” – and for “banks” I have two sub-categories. I would like to mark one of these two sub-categories and “banks” should be auto-marked. I have succeeded to do this, but then I can’t mark only “banks” if there is no need to mark sub-categories… What am I doing wrong? Or this is not possible? Thanks in advance.

FMP8 Adv, WinXP I need to create a new ID field (number, auto enter serial) in an existing table but the table already contains more than 4000 records. The current ID field in this inherited file is based on a name (text) – not number, auto enter). How do I enter the new IDs using a command (or script?) so that every record will have a new/unique/sequence number ID in the field? I only need to do this once in order to place a new ID in each record. I’ve read info about serial auto enter IDs, but I can’t find anything on how to input the serial IDs in an already existing table with records. TIA lpm

Hi all, I'm a newbie to FMPro so I hope someone can help with what is probably a simple problem that has me tearing my hair out. I'm making a datbase for a small business. The database will be logging each client enquiry that comes into the business. It will also be used to record the existing clients the company has. So far I have made a database where I can enter clients in one layout and then enter client enquiries in another layout (basically the simplest thing in FMPro!) I would just use a lookup but half the client enquiries come to nothing, while the other half actually develop into proper paying clients of the company that then need to go into the c…

Hi, I'm having a problem with the following line. I have a file sales, which has a relationship to data_5 (sorry for the name), data_5 stores all the individual items. What I do on my command to print an invoice is: Go To Related Record, Show Only Related Records, Match Found Set, From Table data_5, external, using layout current layout. Right when I do this (going in debug mode) it adds an extra blank record in data_5. I don't know why it does that or how I can get rid of it? Does anyone maybe have an idea? Thanks Robin

Is it possible to create a child record before creating a parent record? I am trying to create a solution where a field in the parent record ("Date") is dependent on a field in the child record ("Date"). dan

I've been asked to improve the way we track our software. I've developed a solution that works pretty well but has a few holes and one of them is very large as I see it. I hope someone here might be able to help open my eyes to a new way of viewing the problem. The trouble comes from some software needing to be assigned to a person/user and other to a device. Another hurdle is licensing. Originally when most software was a single license it was easy but now when buying a 200+ license for the same title with the same install code/serial number it gets more tricky. In my solution I have Users, Devices, and Software. Each software record is currently designed t…

Hello all: I'm exteremly new to this wonderful forum and to filemaker itself. My question is regarding toystore system that I'm trying to make (and am not sure if this is the right place to post this!). This system is to store all items info and track all transactions (mainly purchases and returned items). I created 6 tables: [color:green]toys(toy_ID (pk), toy_description, category, original_price, supplier_id (fk)). [color:green]suppliers(supplier_id(pk), supplier_name, supplier_telephone, supplier_address). [color:green]jncttbl (supplier_id(fk), jncttbl_id(pk), manufacturer_id(fk)). [color:green]manufacturer(manufacturerID(pk), manufacturer_name, country_id(…

Hello, I am new to Filemaker and really my brain just doesn't seem to work well for this sort of thing. I am trying to set up a database to track documents in an archival collection. We need to track each thing for intellectual property permissions/copyright. We need to be able to enter in item info, like what it is, whether it's under copyright etc, along with the authors, and publishers information. From what I understand this is a many to many relationship (each document can have several authors and/or publishers and each publisher and author can have several documents). I'm not even sure I set up my tables properly and then there is the larger question of how do…

I'm not sure, but this has to be possible. I would like to have a field that is a Check Box show looked up values specific to the type of unit. Basically user puts in say "Ford" then Option field populates with available colors for "Ford". I have an options table to store the options. The lookup works fine if the field is a regular field. But I'm not seeing the way to allow it to be a checkbox field due to having to make a value list. I don't want to show all the options for other units, only ones that pertain to the specific type. Little Help? Thanks, Michael

Hi All, I am not 'new' to filemaker but have had no success tying in two tables. I have just created a one table database for a magazine that comes out every other month. Additionally, the magazine also sells many different items that have different submission dates. Example, website sponsors every 4 week minimum, enewsletters every week, etc. The database is built to hold 4 years of data as one contract can hold 2 years. I now have to build the invoicing (all items invoiced differently) and detailed reporting. I know i can do this more efficiently if i build separate tables for these two things but am stumped on how to start. Each company holds a control number and eac…

Hi everyone, First, not to new to FM but still not exactly an expert. I am trying to create a purchasing database. Our business is unique in that most services are hard to classify in terms of categories. I feel the best way to classify Vendors is by having a notes field that I can type certain industry terms in, then do a *_field search(i've attached a copy of my file). I originally wanted to just relate the g_productSearch and start typing and have vendor's start listing through portal. Couldn't figure out how to make it work so created script to do search. How can I list the found set in the portal? I know I could do a "report" but I may not want to us…

Hello, i am creating an inventory database to store item stock as well as keep track of suppliers. I currently have a portal that shows all available supplier parts No's and prices. I print barcodes that go with each of these items to be places below the item on the shelf. I would like to include the manufactures part number and price as well. The problem is FileMaker chooses for itself what supplier link to use, currently the supplier name and part number do not reflect the same record (BAD). Right now it chooses the lowest SupplierID and lowest SupplierPartPricesID, not neccessarily from the same record link, one can only see the problem when theres more than two suppli…

Does anyone know if using MacSQL 3 will allow me to insert and retrieve data from a filemaker database to a front end made using Applescript Studio?

Currently, I have a table with all the zipcodes. I have no problem in have the lookup provide me with the City, etc. But here is my problem. [color:red]SOME ZIP CODE have multiple Cities Example [color:green]01062 State is Massachusetts, but the cities can be>>> 1) Bay State Village 2) Florence 3) Northampton Of course the lookup just grabs the 1st one. So, how can I have it show me all 3?: [color:orange]Value List, Portal, Script : Any ideas or code examples would be GREATLY APPRECIATED :thumbup:

I want to manage my DVD collection (documentries), and my dilema is how to go about it.My DVDs would be purchased or recorded. The recorded DVDs could contain many movies, some fields in purchased would not apply to recorded ones. I attache a sketch of my 2 tables which I am after. Any hints in fundamentals how I can achive it, please!

Hi I am using the clairevoyance option on a textfield. The dropdown-list source for the textfield is a field in a related seperate file. I created the relation of that seperate related file in the relationship graph. Everything works OK but the Type-Ahead is too slow. (1000 records in the related file) Do I get more speed when I import the related file as another table in my database-sys. Thanks

I created a database that has a lot of relationships and portals. Everything works except one place and I'm wondering why. I have a book table, person table, penname table, assignments table, a sales table, and a royalties table. The book table creates a book and then uses the assignment table in a portal to create new assignments of people of each book. The sales table creates a sale and uses the assignment portal to figure out what the royalty will be for each person. The problem is I'm using assignments portals instead of royalty portals. Maybe I just answered my own question, I should put a royalty portal in there and use the child related assignment ta…

Hi, Is there a way to relate a repeating field to itself, so that the relationship will be true if one of the repetition values exists in any repetition of that field in any other record? For example record 1 repetition 1 - value "apple" record 1 repetition 2 - value "grape" record 1 repetition 3 - value "pear" record 1 repetition 4 - value "banana" record 2 repetition 1 - value "peach" record 2 repetition 2 - value "orange" record 2 repetition 3 - value "apple" record 2 repetition 4 - value "peach" so, in this case, on record 1, the 1st repetition would indicate that it was related to record 2, repetition #3, and vic…

I'm working on my first ERD. I've developed FM databases in the past, but it was about 10 years ago, so things have changed. And at that time, I was flying by the seat of my pants. This time around I want to learn how to do it right, and I'm struggling a bit. I mean, I understand the mechanics of it, but I don't have the wisdom to be able to look at my structure and see the problems and/or solutions. Any suggestions? I'm reading Using Filemaker Pro, and it's a huge help, but I think I need someone to take a look at it and give me some tips.

I have a simple two table database. What I would like to do is have a button that will take the user to the related record if there is one but if there is not a related record then I want to pop up the custom dialog box. Can someone explain how is this done? It seems pretty simple but I can't get it to work. Thank you! Becky

I have been a FM user for data entry for a number of years, but I was recently charged with putting together a simple FM db for an auction event that my nonprofit organization is having. I'm a quick study, but I've been given a short time-line, and am having trouble figuring out the names of the concepts that I need, so I haven't been able to search for help effectively. So, please forgive me if I'm posting this in the wrong place, or if this question was answered recently. I just wasn't able to find the answer. I have two fields that I need to relate, and they need to have a one-to-one relationship. I think I need to use value lists for this, but I'm not su…

Hi everyone. After extensive searching I have not been able to find a conclusive answer to my question--your expertise would be greatly appreciated. Although I have fairly advanced skill in computers, I am a complete novice at FileMaker. I know enough about relational databases to "get by" but still have much to learn in order to be autonomous in my projects. So, that being said, here is my question: I would like to build a "workout plan" solution, to be used by both trainers and their clients. The trainer would input a workout plan for each client, including the name of the exercise, the number of sets, and the number of reps per set. This would be cross-r…

i have a basic fmp file with 2 tables. they relate via a job number. in my job specs table, i have a portal which shows fields from my schedule table. once a job has been billed and closed we can remove it from the job schedule which i created a delete record button for. i want the portal in my specs table to not be effect from the record deleted in my schedule table. i unchecked the area in my relationship which states "Delete related records in this table when a record..." this still removes the information in my portal. Can someone please advise me to how to delete a record and KEEP all that information in a portal? its killing me here. :

I am working in a table which contains a portal linked to an "Invoice items" table; the portal displays invoice amounts, and also a radio button with two choices: "taxable" or "nontaxable/labor" (The radio button data actually comes from another table, "Billing_Codes.") I would like to insert two fields: one is a summation of all "taxable" invoices and the second is a summation of all "nontaxable/labor" invoices, as seen in the portal display. The following calculation I have set up is not operational: Sum ( If (Billing_Codes::Taxable? = "Taxable" ; Invoice_Items::Amount ; 0) ) Any suggestions for calculating a sum of only certain items, depending on which rad…

Hi all : I have a school-based FM7 DB served using FMS 7 on an xServe to Mac clients (some thru hardwire connections, some thru wireless). PROBLEM: Twice in the last two weeks all the visible fields in all the records in the "students courses" table have lost their data - the records are still there, but all the data has disappeared - text, graphics... everything. ***student courses RELATED TO student details through unique Student ID*** Now this database has been running on this server for almost 3 years without this happening... altho I seem to remember a similar problem back in 2004 when I first built it. You can create new records and a…

I'm going blind reading forums and FM help screens! I've got some dbs created in FMP5 and updated to FMP7 that need to change relationships. But, I just don't get how tables work. I have databases for several pieces of equipment containing the elapsed time of use for different users. Each user has a unique name. I'd like to keep these dbs separate and then use a front end db to create summaries and subsummaries with breakfields based on budget numbers linked to each user (kept in a separate database). But, I keep getting hung up on how to get the tables to reference the other files. Or do the tables refer to related fields defined within the front end db? …

I'm trying to get this database to do a lookup of MX Costs (in a table in the same file) and place the Article #, Description, DKK and Net Landed into the appropriate fields on the Pricing Worksheet. All I get is the Article number right now, although I can see the description and article number in the drop down list. I can't figure out what it is I'm doing wrong. I'm also trying to get a Filter Value list to work, so that it narrows down the list to certain categories, so that the user has less to scroll through. I've attached the Filemaker file for you to look at. Product_Pricing_Calculator.fp7.zip

I'm a FileMaker Novice. I've created a small database indicating several testing rooms and when they are in use by students. The DB contains several tables (room, equipment, student info, and exam info. A report shows the times during which students are in a particular room. I'd like to show time conflicts where or not there might be a time conflict (i.e. two students in the room at the same time). Is there any way of doing this? In the EXAM table, the instructor enters his/her test info (beginning time, date of test, and length of time to take the test), an administrator books the room. If there is a conflict between the beginning of a test time and t…

I'm trying to get records from three different fields to display under ONE column on a report. For example, I have three different job titles (PD, AR, ED) which I would like to display under a fourth title, Contact. Does anyone have any suggestions on how to do this? Thanks.

I finally "got religion" as far as using join tables a little over a year ago, but -- having developed some bad habits with clunky but serviceable relationships -- still have some structure/design questions. We are a book business and, in the old days, our way of assigning costs to individual services for a book (printing, editing, etc) was to just fill out an enormous worksheet with a blank field for every service we offer. Clunky, but it made it very easy to "look back" from a related table and grab a service's cost. Since implementing a join table structure to tie services to a book, I can't see how I'd easily "look back" to grab a single service's cost from…

Let we have a relation between teachers table and students table (0ne to many). Is it possible in a students layout to show automaticaly the name of the students teacher based on the selected class in both of tables? If the user selected the same class for teacher and for the student why have to complete the students' teacher field, this is the concept. How the relation the will be and calculated fields as well? Pascal

Hello all, I've been working on a new filemaker solution for my biology lab to keep track of our production and stocks. I'm approaching a certain aspect that I am having trouble figuring out how to present to the user. Well, to be truthful, I'm still working on how to model the data as well, but I think I have an idea on how to approach that. So here's the problem. Most of our stocks are stored in small tubes that we keep in small boxes with dividers, separating the box into a (sometimes) 9X9 or (usually) 10X10 grid, so the box can hold up to 100 tubes. These positions are represented with letters for the columns, and numbers for the rows. So individual po…

Hard to find correct forum for this question, hope this is ok: I've been doing a lot of reading and thinking as I develop my first 'serious' solution, after years of using FM as a quick and dirty tool to help myself track jobs. So I read, and it makes sense, that every table should contain an auto-entry serial number, creation and modification date stamps. I'd like to be able to create a 'default' table with these in, and just duplicate it to start a new table. But there is no duplicate button. I suppose it's not something you'd want to do often, and could be a very dangerous tool. So is there a quick way of getting default fields into your table? Or am I being…

Can anyone offer me some guidance setting up a user preference table? Or direct me to simple sample file

Hello, I am a novice at filemaker 8.5, but with the help of FM Forum have managed to create a successful invoicing solution for the company that i work for. This has been loosely based upon the business productivity pack. (See attached picture) This solution has repeating field which i've heard are not the best. However, my problem is that the management have asked me to re-design the system so that each customer will have an induvidual price for each of our products, approx 90 - 100 product in total. My question is how do i form this relationship. Many thanks. Capture1.pdf

Hello all: I have a general question regarding relationships. If you have Table 1 thats related to Table 2 as a one to many and then Table 3 is related to Table 2 as a one to many. My question is when do the records load from the sub tables or in this case Table 2 and Table 3. So for instance if I have a layout and I'm loading a record from Table 1 -- do the records from Table 2 and Table 3 load as well because of the relationship? I guess I'm wanting to know is when do records load in general. If Table 1 can pull records from Table 3 because of the relationship then I'm assuming that if I put 2 portals on a layout for Table 1 -- one portal for table 2 rec…

Firstly, I'm completely new to FMPro (3 weeks) and I'm using FMPro 6 (hoping for approval for an upgrade real soon now !!) The Situation. I am building a Contact DB that will manage three different types of contacts: Sales Leads, actual Sales and a comparison between those two to list the leads that actually bought (Conversions). I started by creating three tables (Leads, Sales, Conversions) but found that I could not find a way to compare the contents of Leads to Sales to find duplicates (this is the primary purpose of this database). So, I created one table and dumped everything in and used a self-join to find duplicates. I also added a Contact_Type field so I …

Hi! Has anyone implemented FIFO method for updating inventory to get real stock value? I don't think that this is possible to accomplish just with relationships. I am thinking to create one table which would have records of orders and sales and with it I could track how many items were sold for what price. Record would be added to this table when I would set invoice status to locked and run script. Is stock table usable like this? I don't know what are best practices to move products from one warehouse to another. I've atached schema of database and xls file to show how I meant to create whole thing. Is it totally missed or am I on the right way? Thanks DBS…

Hi. New to the board and fmp. I was wondering if any of you know if fmp is capable of automatically adjusting my inventory. We make small devices that consist of several parts. When I remove one of the devices from my inventory I would like to have all of it's component parts removed as well. Hopefully I explained that well. Thanks for any help and for letting me join.

Hello everybody! I'm new to FM, i've been into programming for many years, but nothing such as this. So i really need some tips that can get me on the right way. I'm designing a database for a medical supplies company, and got stuck on the very beginning. I have the spanish version, so please understand if some words are not accurate. I have a table with a lot of fields. Some of them auto-complete themselves if some other field already had that info. To make it clearer: In the Layout1, fieldA=Siemens Sensor, fieldB=RoomH 32-A. If I enter "Siemens Sensor" in the fieldA of layout 2, the fieldB automaticly fills itself with "RoomH 32-A". To do that, I c…

Hi, I try to write an on-line examination system. In this system, I have an item store table, which hold all tests item for student. Once the student create new examination, the system should copy some records from the store (for example, 2 of 3) randomly for this examination. How can I to define the relationship? Sample file is attached. Thanks in advance. Exam_2007.zip

My database is for organising and formatting text for various publications (sale leaflets etc). I have to expand the version I built for a single publication to allow many publications, many users. I've created a table called Publication which will hold Publication_ID (auto enter serial), Publication_Name and Publication_Status (Active or Inactive). When a user logs in for a session, I want them to be able to choose, somehow, from a list of names of active publications. If I can get that to work, I'll set a global to that publication id and use it to ensure they only see data that relates to their chosen publication. But how do I do this? There is no meanin…

I am new to Filemaker Pro, btw, so please explain fully in explanations. I am working on planning a database to help me organize a CSA program. Community Supported Agriculture. Basically this is what needs to be organized: Members sign up on a quarterly basis to purchase baskets of produce from our farm. They have the option to choose to receive large or small baskets, each with its own price, obviously. They also have the option to receive it every week or every other week. For the biweekly crew, we would like to separate them into two groups (1st and 3rd week // and 2nd and 4th week) in order to distribute the load of baskets to make each wee…

I have a database that holds all of our projects. I started it in 1998 and our project numbers from then forward have always started with a 2-digit year (ex. 07085 would be the 85th project of this year). We recently decided to add all legacy projects to this database which were 4-digit numbers with no date relationship (ex. 7085). Now that those projects have been added when we use a form that is related to the project DB and a user enters 07085 for the project #, FMP pulls the info from 7085 (ignoring the initial zero). Is there a way to make FMP compare all numbers and not ignore leading zeros? Thanks

I need to create what is a union of 2 equi-joins in a single portal. Can it be done? The conditions are mutually exclusive under normal circumstances. On one side of the relationship I can select either a type OR an id. The purpose is that when I select a type, I am creating a grouping that will be assigned an id. Once I assign an id, I can only view those records in the portal by selecting it's id. Now I have to be able to edit that set assigned the id which could mean adding or removing records from that group. That means I have to be able to view the records from that ID AND distinctly the available records of that TYPE. Creating a separate portal to d…

I have one table called Tours, and another table called Personnel. It would be a many to many relationship as one Tour will have many Personnel, and any one Pesonnel record can be part of more than one tour. OK, so I get the many to many part and know I have to create Join tables. Onto the real question. Each Personnel record is distinguished by category (Artist, Travel Agent, Technician, etc.) and associated to the Tour table in only that particular way. Each Personnel record is set up so its validated by a specific Category. The end user cannot create a record without choosing a Category, so that part is done. What I need to do is build a distinct 'set' of ass…

FMP Adv database with a relationship with another FMP Adv database has started returning the error: "File has been forcefully disconnected by the host. All affected windows will be closed. [From Administrator:""]" Along with the error is a single "OK" button choice, when button is clicked all information dependent on the relationship goes missing. This does not happen for guests or data entry users, just full access users. Any ideas whatsoever?

Hello, Thanks to Mr. Vodka's help, I was able to create a pop-up window for my "Project Hours" database (See Topic#186984). Now, however, I added two detailed reports, but the layouts are not working together. I cannot add more team members to the "Hours Log" for some reason. I am still new to relationships, and cannot figure out why. I have attached a copy. Any advice?? Alisun Hours_Log.zip

I am new to File Maker (using version 8) and it's been awhile since I've designed a database, so I'm hoping some of you can help me out. I've only ever used Access and some things are tripping me up. Maybe I've created them incorrectly or not in the smartest way, so help would be greatly appreciated. I have the following tables: Customers, Members, Plays, Contact, Invoices, and Payments. Customers and Members are the only tables that do not have to relate to one another. Within this organization, Members are playwrights who sell the rights to their plays through us. Customers are individuals or organizations who purchase the rights to a play. Therefore, Plays are rea…

Hi, I hope someone has some good advice here for this one: I work for an organization that runs two large trade shows each year. Currently our FMPro database only has one contact person, one phone number and one email address for each company we deal with for both shows. This is adequate for 85-90% of the companies we deal with who have the same contact info for both shows. It is also helpful, as we do a lot of exporting to excel for others to have just one set of fields to get. There are a number of people using the database that are not very proficient with databases/computers. So simplicity is key. I am rebuilding the database from scratch and I am consi…

I have an address list with many duplicates (eg., John&Susan&Bob...at the same address) and I need to create a mail list to housholds-one mail to each unique address. Please help. Thanks

Hi all I have two db for welders. The first db is active welder and the second one is inactive welders. The inactive welders are welders that are no longer working for the company. Some of the inactive welders created welding test to verify welding compatibility between the filler metal and the the base metal. I would like to removed thesse welder from the active db and install them in the inactive db but I still need to look up the inactive welders clock number, name, and stencil number when these welding test are printed for use. That would take a double look up. Is this Possible. Every thing I have tried have not worked. Thank inadvance Lionel

I am a newcomer to FM, tasked with setting up a solution to track foreign language translation assignments. I got a FileMaker book, studied up, and produced a rough solution that has been in use for a couple weeks now. After a few tweaks, most things are working. The translating projects involve specific text segments that go from English into one of a handful of target languages. There is a TRANSLATORS table, where each translator is associated with a particular language. There are several translators that handle Spanish, several that handle Russian, etc. Assignments are made using an ASSIGNMENTS table, and when each assignment comes back, it then gets assigne…

Hello, I'm having trouble wrapping my head around how I need to structure my relationships. I trying to do the following. Database users will enter workorders into the WORKORDERS table for clients from the CLIENTS table each workorder has multiple service line items. After multiple workorder records are complete for a client I would like the database user to be able to goto an INVOICE table and select a clients name then in a portal below they would choose from a drop down list that was populated with that clients related workorders. I am very new at this and if any further information is needed please ask. I've also included a copy of what I have thus far. Jaso…

Hi Folks! I know you can't use a calc field to establish a valid relationship; in the past I have used text or number fields that have auto-entered calculation formulas that depend from Case(IsEmpty ... instances such that when a field gets a value, the calc occurs in the text or number field and the relationship works. I have a calculation field (calcfield1) for invoice records that toggles between two values: "Complete" and "Incomplete" and I want to create a relationship to show only "incomplete" records in a portal via a relationship. Accordingly, the field value never IsEmpty ... Is there formula for use in a auto-enter calculation in a text field th…

Hello! Thanks in advance for any help that y'all can offer. I'll try to keep it simple and illustrate my problem with a contrived example. I have a set of records from a veterinary clinic for dogs. Each record contains the dog's name, the owner's name, and the breed of the dog. Because the system is structured from the perspective of the patient (the dog), there's a record for each dog. Because a person can own multiple dogs, there might be more than one record that contains the name of a given owner. I need to be able to create a query dialog for a user to enter a specific breed of dog and get a list of everyone who owns a dog of that breed. But, if …

i'm new to file maker. i would like to make a database that will manage customers, invoices, inventory and correspondence. Ideally i would like to have a button or something on my main customer screen that will create an invoice and automatically copy all of the customer's information. i think that once i know how to do this i will be able to create whatever scripts are necessary to do all the other stuff i want. Thanks in advance! Todd

Hello, Am stuck on a little problem, I have created a purchase orders line items file and want to update my inventory with the newly ordered items. I thought of two ways to accomplish this but am not quite there and was looking for expert advice. I would be ordering the same items several times and would like to press a button and update all the qty's in the stock table and if I have never ordered that product before to just add it as a new record. I tried exporting from Purchase Order Line Items into a new temp file then importing in to Stocks, with the barcode field as the matching field so it would only update the qty and add non-matching as new rec…

I'm on Layout "A" Which shows records from Table "A" Table "B" is a related table to "A" (B is child to A) I would like to change a record in Table B and commit the change without leaving layout A. Is this possible? I'm really trying to reduce all the "Flashing" that occurs when I change layouts. Even if I change from one layout to the other and the layouts are Virtually identical, I still get the flashy thing going on. Thanks, Mel

I was just playing around with FM for a while and mentioned that it might be a better way of tracking out business to my boss. Now, all the sudden, this is a project that needs to be done yesterday, regardless of the fact that I have no experience with database design. Our needs are fairly simple, so I decided to tweak one of the templates, but I am having some problems. In the registration template (FM 8.5 for Mac) there are 3 tables defined. In the relationships area, there are all these other tables used to relate things to one another. I have no idea where they came from or how to edit them. I am trying to create a relationship in the contacts are…

Hello to All! I posted this in portals, but in retrospect, its a relationship thing... Any suggestions would be gratefully recieved... With thanks ------------- Hiya... I'm working on an Event's database, that incorporates attendees info, events and the people who organize events. Event teams tend to be fairly static with people repeating roles regularly. As they are an Event team member, they also show up in the attendees table, as they've attended an Event. I'm wanting to display events that individual attendees have been involved with, and the specific role they played... ie, Jane Smith 'ORGANISED' 'EventID' 001 & 'ASSISTED' 'Eve…