Relationships

Below was my first post... The work around I found is if I have a Dialog Box and ask for the Test field, I can enter the Quote Number, then set that as the variable and everything works fine. However, I can't figure out how to set the variable automatically. It always pulls the first or last one. I have a table called Quotes. Each quote may have 0 or more Flyers. They are related by the Quote ID and I have a portal in Quotes. My goal is to add a Flyer, which makes a nice PDF of the FLyer with the info I put in. THe problem is, all the Flyers have the same Quote ID so they don't Save As PDF correctly. Here is my script: Commit Records/Requests Set…

I've attached a mock database where I am trying to use table of occurrences to display a subset of the data in a portal. I can't seem to get the ANIMAL field and the DATE fields (highlighted in yellow) to show the correct data for the new occurrence tables. Instead they show only one value, presumably the first value of the table. Why would the portal on the left show each individual animal and each corresponding date, while the portal on the right show only the first value? Sizes.zip

My db is set up with contacts info also shown with other portals to tables for address, phone, email, web, notes, etc. Each table has a contacts foreign key and all contacts_"other-table" relationships work Now I want to create lists for the tables and I can't figure out how to show the contact name in those table's lists. For example, with the address table I've tried: address_Contacts contacts_Address_RelatedFilter but neither results in showing the contact name in the address table. This should be so simple - what am I missing?

I have many to many relation. ( T1 ---< JT >--- T2 ) I created two layouts based on T1 and T2. Both layouts work in List View. For several days I'm looking for solution of the following problem: JT (Join Table) represents history relations. I stand on the layout T1. I want to display related records from T2 but only the most recent ones. ( T1 ---< T2 ) . many to many and GTRR problem.fmp12.zip

Hello, I'm not that new to db design (started with access and migrated to fm) but I'm having a bit of a senior moment. I have two tables. One is an inventory of equipment, the other is of staff members. I want to create a table from these two that has one record for each staff member for each piece of equipment with additional new fields for whether the staff member is trained in its use. So if i have 20 staff and 300 pieces of equipment i'm expecting the new table to have 6000 records. the staff table would also have regular additions and the equipment table less so. I understand relationships and in ms access i would do this with an append query to new table b…

Hello, Iâ��m new here, so I suppose that my question and topic of the database were discussed many, many times:), but unfortunately I donâ��t understand everything, thatâ��s why I want to ask people who have much more experience with databases. Becouse I need to exceptionally some simply database in Access, with is a bit something new for me. Topic of database is locker-room for production crews, so itâ��s a unit in factory, which assign for every employee a trousers, shoes, helmet, glasses and many other things, when He start work. Every employee also has of course individual locker. So for example. Mrs. John start His work in Juanary 2012, and He get: 4 pair of trou…

Every week I upload a spreadsheet with 1000 "jobs" on it. Each job is one service at one location for an account. Each account has an account number. There are about 500 "jobs" per week. I need a process that adds "new accounts" but does not duplicate Logic looks like Step 1 For each new job lookup customer number in existing accounts list If this account number exists go to step 2 If this account does not exist create a new account with this information and account number - now go to step 2 Step 2 put job ticket into jobs table Its been a long time and many versions ago since I used FM. Any help appreciated. Or if this is wrong forum …

Edit: Sorry but I realized this might be more fitting underneath the functions section of the forum... my apologizes. Hello FM Forum, Long story short, I'm fairly new to FileMaker and I'm trying to create a database from scratch. At the moment, I have 5 tables. Stamp, Album, Type, Collection, and Home. For my layouts, I have a Stamp_List, Stamp_Detail that are based off of Stamp table, Album_List that is based off of Album table, Type_List based off of Type table, and Default_Home (which is basically an utility layout that is based off Home table and has no fields at the moment) What I want to do is create a function to counts the total number of recor…

I have a relationship set up between two files (not tables within the same file). I'm then using a GTRR script to go to related records that are sorted as part of the relationship criteria. When I run the script it doesn't go to the other file, however if I manually go to the other file the related records are displayed but not sorted. As part of the GTRR step I checked 'new window' and the script works perfectly. Does anyone know the reason for this please?

This may be simple and obvious but I'm still learning. (FM 10 Adv) This is for quoting / invoicing in a screen printing business. I know how to setup up simple invoicing using a table of "Orders" and a related table of "Order Items". The problem I have is in the pricing. Pricing is dependent on two factors...quantity and number of colors. I just can't figure out how to get the information I need or how I'm even supposed to set this up. It may be confusing to understand so I'll try to explain. Say I have a line item for a 100 Gildan shirts. I pull the shirt information from a related table that has price and description. The next field is how many colors are in…

Hi Not sure this is the right category... what I want is a checkbox field for each record in a contact table, but it should act like a global field, only for the user, who checks it. The goal is that a user could find a list of records, then mark some of the records and then run a script that e.g. prints the selected only. The problem is, that if it is a standard checkbox, another user could check or uncheck this box, before the first user has been able to run the print script, which makes the wanted set of records unconsistent. Every user has their own interface file local on their computer. The datafile is located on a server. Any suggestion to h…

I have been working with FileMaker pro 8 advanced for about 4 yrs now. However, I was not aware how powerful this database was. I have been using it as a database for storing records for an oil company that I work for in Houston Texas. Mostly a static 1-dimensional use. I am able to create most of what I need it to do, such as run sheets for title attorneys & basic record keeping for my own personal use. Most of which I have done using check boxes as a sub sorting tool. I have signed up for the VTC training, but really only have a very specific need at this point which it looks like from what I have learned so far will require some type of complex script. Ho…

Hi folks, I have a many-to-many relationship where the join table includes a date. I want to sort the relationship by that date in descending order. For example, let's say I have Buyers <-> Sales <-> Sellers, where Sales is the join table. Then I want to sort by sale date in descending order. When I show a Seller in a layout attached to Sellers, I want to display information about the Buyer involved in that Seller's most recent Sale. I want to do that with a related field in the layout, not a portal, because another task I need to do is to send e-mails to each Seller where I remind him who his most recent buyer is---and for those e-mails I need t…

I have an extremely simply relationship in Filemaker 12 Authorization -> AuthorizationLineItems The authorization table has about 20k records and the AuthorizationLineItem table has about 90k records. When performing a search on an indexed calculation field directly on the AuthorizationLineItem table this search takes less than a second. However, when searching that field same indexed field through the above relationship from the Authorization table, the search takes in excess of 5 seconds to complete. Is there some fundamental issue with the above relationship that I'm not aware of, or is Filemaker 12 WAY slower when it comes to related searches when …

I have a main inventory table with general data and a related table with inventory details in a one to many relationship. I would like to enter the main info and the detailed info in one window/screen. Could somenone help me out to get this done, if it can be done at all.

Hi folks, This question seems too simple, and I can figure out a number of ways to do it, but I want to know what's <good> and <right>... I've got a main "orders" table, that is a typical orders-with-line-items kinda thing. Happens to be called "Job". I've got an "address book" table, also very typical. Happens to be called "Contacts". I have a join table between these, because a Job can have many Contacts - "sold to", "ship to" "carrier", etc. And obviously a Contact can have many jobs, and many roles - sometimes "sold to" is the same as "ship to", sometimes it's not. User Interface: Job records are initially created in the Quote…

Hey there. I'm trying to build a BOM management system, which is proving to be difficult. Honestly, I'm not a Filemaker expert and I've never attempted something like this before. I was thinking of having a report-style layout with sub-headers dividing layers... but I have no idea how to do something like that (if it is possible) recursive-style where "infinite" layers are possible. Another issue was that I have nothing to determine the correct sort order with if I were to display the records by some means. I found the following: http://www.spf-15.com/fmExamples/ Which contained a basic BOM example where I pulled the recursive text List generat…

I have a problem with sorting many to many relationship. I have three tables. TAB1 - <TAB1_TAB2> - tab2 In layaut based on TAB2 I put a portal (TAB1). Table TAB1_TAB2 include the fields of foreign keys and filed counter. I would like to see the data (TAB1) in the portal sorted by the counter from (TAB1_TAB2). many to many SORT.fmp12.zip

I have two tables customers and payments. In customer’s table I can see the total amount of payments of a customer. What I want to do is to add a date field to the relationship to filter the data shown. I.e. If date field is empty in customer’s table, all payments and their totals are shown but if I place 2011, only those payments are shown and only the totals for that year. I know it must be simple, but I have to admit it I am completely lost with relationships.

I'm just coming back to FM and finding some difficulty on creating, what seems to be, a simple relationship. In our database we have Inventory Items. A single item can be purchased from multiple suppliers, ex: a pencil. We also get different pricing form each supplier. A supplier is just a company that provides an item, a company is used elsewhere in our database as well. The basic schema is attached to this post. Here's what works: - I can create an item - I can create suppliers for the item in the portal What's doesn't work: - When I create a new price tier, it creates a new record in the supplier table and sets the SupplierID incorrectly in…

Help! This should be simple but has been driving me crazy for a week solid now and is not looking like I'm going to figure it out anytime soon... What I'm trying to do is to keep track of individual scores for a sparring competition. I'm concerned with total for the month, quarter and year. Each quarter is simply Sparring Month 1 + Sparring Month 2 + Sparring Month 3 (for Quarter 1) The Year is also simply Sparring Quarter 1 + Sparring Quarter 2 + Sparring Quarter 3 + Sparring Quarter 4 There are 12 fields for the months labled Sparring Month 1, Sparring Month 2 and so on. There is also a seperate field labled Sparring Additional Points…

Should money coming in (AR) and money going out (AP) be in two different tables, or can the be merged? I feel I should merge the two and just have one table called Transactions, then just label entries a deposit or withdrawal. But maybe this is against standard accounting practices. I could see where a merged table of AR and AP could get cloudy. e.g. deposit/payments would tie to Invoice(s) and withdrawals would not. What to do, what to do? THANKS for your help! :)

Hi guys, I hope you are all well. I'm fairly new to the forum and this is my 1st post. I am using FM12 to set up a Customer Management system. I have recently gone back to being a self employed massage therapist having been unemployed for a time and going bankrupt due to the property/financial "problems" we currently have. I've used FM before (FM10 I think) so this basic set up is really frustrating me, I've got info overload and worst still cant find the copy Filemaker Bible I brought last time i created a database the way i wanted it. I'll try and give as much info as I can (sorry if its to much) so that any other issues you guys can see may be flagged up…

I have a food cost app with three primary tables: Inventory/ingredients, recipes, menus. Where I am stuck is when a recipe needs to be used in another recipe. In the recipe table there is a portal to recipe_child which are ingredients. I want another table back into Recipe_parent. Here are a couple of examples: Hamburger: 4 oz Hamburger 1 ea Bun 1 ea Condiments Cheeseburger: 1 ea Hamburger 1 sl Cheese ChiliBurger: 1 ea Hamburger 2 oz Chili I have a Recipe TO related to Recipe2 TO with the Portal to look in Recipe2. That is not working. What am I overlooking?

I have made this sample file to explain what I’m trying to accomplish, that is, to show only the correct button everytime. The show/hide buttons trick works perfectly but only if there’s only one record in the parent table. I know it’s because the relationship happens at last once if there’s more than one record. I have tried to relate the IDs as constant but couldn’t make it work either. How could I make it work independent for each record? Another question: When I create a new record, why does always show the clear child button until I commit the record, even when the correct relationship to show generate button is set. Thank you very …

Hello everyone; I am trying to figure out my relationships based on my bakery model and we have a lot of niche products. here is a screenshot of my relationships( which i am certain are not anchor buoy) I would like to know how can i start this off so i dont get trapped with a maze of confusing TO & TOG's that make no sense. any help would be awesome. The problem i am already seeing is i have to enable pull aparts, suites & cakes to "Allow creation of records" while using a layout based on line items table based on dept_ID & dept_fk. ProductID is connected to LIneItems with product_fk. how can i get this neater …

I have 2 tables - Inventory and Production. We need to take inventory of those items that are scheduled for production. I only want the inventory database to show the subset of part numbers that are present in the production table. How can I do this with a relationship?

I have a global calculation field which acts as a reference for a relationship... But it doesn't work. If I use a global number field and enter the same data manually as is calculated in the global calculation field, the relationship works just fine. Weird or am I doing something wrong? As I am making a touch screen solution, a four digit sequence ( eg. 2395 ) is made up out of 4 global fields ( eg. field_one & field_two & field_three & field_four ). Each global field can be manipulated by a plus and a minus icon, going up and down from 0 to 9. Very simple, works fast and flawless. Except for the generated number not making a match with related table. …

Hope you're all having a great day. I have a Table of families and a related Table of children, related by ID. On occasions, we need to change the ID of the whole family as they move from 'inactive' to 'Active'. Obviously chnaging the ID of the family leaves the children wondering on their own unrelated to that family and I then ned to go in, search for the children and chnage their ID's as well to get them back with their surname and parents. How can I automate this that I just change the family ID and both the Children's ID's chnage at the same time? Thanks so much - I really appreciate it.

Part of my solution needs to advise users which are 'good' days to book particular trainers, and which are 'bad'. (Good or bad depends on distance from trainers home and whether the trainer can train that product.) (I've built a matrix of post codes -> Regions and have a join table vs Trainer (i.e. Trainer A likes region A, Trainer A doesn't like Region B, Trainer hates Region C etc) (I also have a 'suitability' join table of trainers to products) My issue then becomes relating which days these trainers are not otherwise occupied or on holiday/sick/PD etc) I can't see past having to create an 'availability' table with every day, every trainer, AM/PM …

Hi there again! Here's a little puzzle from a relative novice (me): I have a parent table and a child table in which I want to create new child records when and ONLY when either <one> OR <both> of TWO fields is unique. Multi-Criteria relationships don't do that... or is there something I'm not seeing? I've experimented, with the multi-criteria being "Parent Field A=Child Field A" AND "Parent Field B=Child Field B", via a portal, when the first field in the child record is made non-empty and exited, a new record is created, regardless of whether the child field was a match field, and all empty match fields are filled with the data from the matched dat…

Hi there! This may seem like a totally obvious question, but I've read the internet, and it says that "Allow Creation..." is for use, basically, only with portals. But what if the related table, where you want to make a new record, is not one with any fields on the layout? Can you use the relationship to create new records via a script, or is that just another one of those "don't try that, it's stupid" kinda things? I'm thinking it <is>. Is there no way to use the "Allow Creation..." trick to create records via a script? I mean, other than having the script "Set Field" (or "Set Field By Name") in a portal? ( I know you can do it without a portal…

Hello to everybody. I really need a help to solve a problem which is driving me crazy. I have the following tables: - Contact: - Information: in this table are stored all the information about the contact, such as email, phone numbers, addresses and roles - Company Each contact has different roles, addresses and can also be member of different accounts. Each company can have different contacts, a unique email address (for example [email protected]) a phone number etc.. Each contact can have different roles and addresses according to the account. This means that Mr. White can work both for ACME and GENERAL MOTORS and he will have an email address…

I am working on a Business Development Tool where we have one table for tracking leads generated from a bunch of sources within the company. This table has some very basic information such as project name, architect, owner, location, etc. One of the BD guys will then vet the leads and if they are good leads promote them to an opportunities table where most of the work is done on the lead. There is no relationship between these two tables since once the lead is promoted out of the Leads table into the Opportunities table, it is deleted out of the leads table. Can someone help me with a script that would allow me to move a record out of the leads table into the opportun…

dear fmforum, my database has many products and each has its unique product ID. each item has 1 to 6 images. i added a "button setup" to the image in the layout. it triggers a "export field content". it exports the image to my desktop with the filename 1,2 ... till 6.jpg is it possible to export the image with the product ID as a filename? e.g. 232345.jpg, 232345-2.jpg ... till 232345-6.jpg sincerely, raitis

I am at a bit of a loss here, I am a novice with Filemaker and could really use some help from anyone that can point me in the right direction. I program a fair amount but normally use a program called Topspeed against a MSSQL database. The way Filemaker does things is a bit foreign to me. What I am trying to dois create a teacher evaluation system for a district and what I need or would like to be able to do is this. When a principal connects I would like them to only see the current year data and also only the data the applies to their school building. Normally with my normal enviornment I would create a configuration file that they would pull up and set the year and th…

I am having trouble getting my head around the appropriate relationship setup between tables in my database. Tables in question include Clients, Contacts, Communication and Projects. Clients (parent) have multple Contacts (child) Communication (phone converstaion, email, or fax) occurs with one or more of the contacts from one client, in relation to one or more projects. I had intially set up a client layout showing related communication via a portal. I wanted to nominate which contact person the communication is occuring with but couldnt seem to get the related name values to appear in a value list. After doing some research it has occured to me that pe…

I am creating a database to set and store summer camp schedules for different weeks. We run 8 weeks of camp, some camps are Sun - Friday, some are Sun - Wed, and some are two weeks long. Each day as slightly different times because of different schedules. Some activities will involve the whole camp, while other activities are broken up by groups (colors). I can create weeks, and set the different days to different weeks. Where I am stuck is going into the days (of the current week I am working with) and setting the times of the schedule. I have tried portal windows, nested portals (which I quickly figured out didn't work), and many different scripts but they have not…

Hello there! I am working on updating my company's current filemaker pro database with some time saving features. Every project has its own record and I am mainly putting in drop down menus of vendor names that will auto populate several fields with the vendors contact information. We have three types of Vendors, will call them vendors A,B,C. A project can have up to two A vendors, 4 B vendors and 4 C vendors. To Achieve this I have my main projects table, two tables for the A vendors(1st A Vendors, 2nd A Vendors), 4 tables for the B vendors and 4 tables for the C vendors with all the vendors as separate records. On my main projects table for the 1st A Vendor Name fi…

This is probably one of the easiest thing one can achieve with a.) relationships and b.) repetitions - but as I have never done much with both features, I just can't seem to figure it out. Table A (Persons) in my DB has fields - Person Name - Person ID (not a repeating field - each person has just one ID and name). Table B ("Groups") in my DB has fields - Group Name - Group Member ID (repeating field - each group has several members) The relationship between the tables is of course A:"Person ID" to B:"Group Member ID". Now in a layout, I want to display and edit Groups (Table B), by: 1.) entering B:Group Member IDs (several values in the repe…

Perhaps this is in the wrong area of the forum, and if so, feel free to move it. I am working (volunteer basis) with a small art association that uses FM for tracking membership. In the past a contractor created a FM database, with multiple tables, for their use. The "memberships" table stores history so there can be more than one record per member. The club would like to place the membership year on mailing labels but invariably the first (oldest) record is selected. Is there a "ready made" way to tell FM to output the date from the last (i.e. most recent) record? I have sorted the memberships file but that had no effect.

I have made an electronic copy of the rules of a sport I play with FileMaker Pro 12. The laws are contained in sections (15 of them). As such there are 15 records and each section is stored in the scrolling field of its unique record. Most of the laws are unqualified. Some though are qualified by laws found in other sections. I want to hyperlink from a law to its qualification preferably by clicking on a text link (as you would on a web page eg emboldened and blue). Because the laws are in scrolling fields I can't used buttons behind the words). Any suggestions on how I might accomplish the feat would be gratefully appreciated. cheers Pabs

Hi all, Been doing fairly well with my cars selling database, with many help from forum members. I'm close to a properly working version now but currently hit another stumbling block. I've created an "all-singing" sheet the point of which is one layout which people can select a car, customer and PDI sheet from to bring up all the relevant information on one sheet. These all link to a master "transaction" with a transaction ID. When you select the customer ID on the front page, the customer page correctly updates with all of the information, but when I select the car the information doesn't come up at all, it's blank? I've attached a screencap here of t…

I originally started a version of this ERD in 1990. There is no more Claris Works on Mac OS Lion to open it with so I'm starting fresh. Actually I am currently using a multiple file database to track information in a law firm that is based on a similar structure, but I have decided to redesign it with FMP 12. After pondering this for a number of days I have come up with the structure in the attached diagram. I know that there are other ways to do this, but this is what my brain comes up with. I would appreciate any input or questions. We use this to track clients, their court dates and court venues, the adjuster and attorneys who are assigned to the file, the expense…

Hi there Am creating a database of children's heights. Have a main table with each child's name and age - call it CHILDREN Each time I see the child I measure their height and weight which are entered into a new table call it GROWTH What i need is when I measure the height on this occasion I want the date of the last measured height ON THIS CHILD to be automatically entered into the record for this occasion. I think I need a self join relationship but am not sure how to set it up so that the height is automatically entered. All help appreciated. Craig McDonald

Hi All, I am looking for some schema advice on how to go about developing a process monitoring tally for a converting line. Basically, I have a printing press which prints a web of material. I currently track data points such as heat temperatures, ink pH, paper roll traceability, and so on, which is done on paper throughout the production run for each roll of stock. The Settings recipe is setup previously which gives the operator targets but then the operator records the actual points on a sheet every roll. What I can't figure out is: 1. How to relate a recipe with an operator entry sheet and then apply those entries to each roll? If anyone has …

So here's a relationship that has worked fine during development, but fails when the file is hosted on FM Server and clients access the file: ID = Order ID AND ActiveDate ≥ First day by licence AND ActiveDate ≤ Last day by licence When I say fails, I mean that starting from a layout of the left hand table, no related records from the right hand table are shown, despite the fact that the relationship graph shows the "crows feet" icon at this point. The ActiveDate field started out as a Global Date calculation = Get (CurrentDate). Having looked at some forum threads, I then changed ActiveDate to a static global date field and populated it when the cl…

I am a total newbie regarding FMP i am mainly a 4D user so i while i get some of the concepts of SGBDR FMP12 is brand new land to me in terms of design and programming. Ok we are in the phase of setting up meetings in between different clients Each clients has his / her own id coordinates etc on the same table , we have created a related table named meetings what we would like to do now is that the event reflects trough a portal on both clients and that the portal shows on each separate client record the meetings occurence. How is it possible with FMP12?

Hi All, I'm trying to create a report equivalent to the attached. The yellow highlights data derived from the parent table; the green highlights data derived from the child of the applicable parent. Problem: The report is from the Child table, and so childless parents are skipped. I've been advised that the way to get around this is to create a third table to report the consolidated data from the parents and children, which is imported on the fly via a script. I couldn't quite figure that out, and would appreciate any advisement you have... also hoping there's an easier way, lol... Thanks! ---J Example.pdf

I really don’t know how to handle this. Lets say tables: Account, Providers, Customers. What I want to accomplish is to use conditional value lists to set a payer into an account line, from two different types (providers and customers). Choosing PRV as payer type only should show the list of providers, choosing CUS only customers one. Type of payer field: value list [-PRV - CUS] Payer field: conditional value list [prv1, prv2, prv3... if PRV is selected as type; cus1, cus2, cus3, if CUS is selected] I think this is a two level conditional value list. Comment and others in this forum has taught me how to do it (more or less understood ) The …

Hi, I am an archaeology student and am attempting to make a database to store and share my graduate supervisors and colleagues ancient DNA data. Aside from one course in Visual Basic in my undergrad I know next to nothing about computer programming. The problem I am having with the database is when one record in one table relates to multiple records in another or vice versa. Forgive my inability to explain my problem using the correct language, I will do my best to give an example of the issue that I am having. The database consists of multiple tables, one for samples, one DNA extractions, etc etc. Each sample relates to a piece of archaeological material that w…

Hello, I am a novice trying to improve my employers contact database. Presently we have a basic contact DB with Company and People tables. I am now trying to add a Marketing Campaign table that will contain the name, description and companies included in each marketing campaign. Each Campaign will have many unique companies and each company can be a member of many campaigns. Ideally, I would be able to create a new campaign record and then add companies to the campaign from a drop down box. I could delete companies from the list without deleting the companies altogether. I've made a few failed attempts. I'm think i'm getting hung up on the many to ma…

Hi - I am working on a personnel database for the law firm I work for. In addition to using it to track employees, they want to track job applicants. They didn't specify that it needed to be a single database, but I think it may be a good idea to do it that way. One consideration is the possibility of multiple terms of employment, for instance a person might work for a few months as a summer intern and then return in a year or more to work as an attorney. So, I'm thinking of using the following tables (also see attached picture) People - basic info, name, SSN, etc., with related tables for address & phone number Employees - with details of employment, e.…

I have a finances file that has a check register, and a budgeting page. Each has it's own table. Each budget period is 2 weeks long, so there is a record for every budget period. There is also a field on the budget page called Remaining Funds that shows how much money is left after all the budgeted items are paid. I have the same field on the check register page via a relation. It had been working perfectly fine, however I've made some major updates to the whole file, and now it's not working. In browse mode, the field on the check register side just say <Index But the relation and it's key fields on both ends are still working. Here's how it's set up. Eac…

Dear All, I'm working on a school system and all going well so far except for one issue. I have a student table, now what i want to do is to generate an "Evaluation Report" about every student after each quarter, here are the details: 1- I want to create an Evaluation template, where i can create a set of questions under different categories e.g: Technical, Admin etc.. and under each category in the report there are a set of questions with some answers (all checkmarks). 2- I want from the students screen to click a button, open another layout with the student name, select the Evaluation Template i already created (from question 1), then all the que…

I and trying to update an existing system for a school. The school get applications that get stored in the Applications table (one a year per applicant). If approved, basic info is copied over to a Student table and have more detailed data added along with school courses etc. Some student stay year over year, some don't. The school wants to "archive" non-returning students. For later reference or to bring back after a year off. Speaking database-wise there can be a few ways to handle this - but none of them strike me as the best method: -Make a copy of the Students table (called Archived Studnets) and copy old ones out and in as necessary. Seems like poor …

I've started building a database to track investments (stocks, mutual funds, bonds, etc.). What I'm after is to periodically duplicate all the records, set a date field with today's date, and update the quantities and prices of the investments. The investments have a field that describes their type (e.g., bond, international, risk, etc.). The outcome is to be able to see snapshots of investments over time, and compare them, as well as to see the current state. I'm sure this is a very common type of database design. However I'm note sure of how to build it (there are probably a number of good ways to build it). Thinking in ERD terms, I was thinking of an investment ta…

Hi All, I've just got back into filemaker after many, many, many years away from databases (the last time was at school) and everything's going fairly swimmingly but one thing's tripping me up. I'll try and explain what I'm doing so hopefully the solution will be quite obvious. I'm doing a database for a car business, that sells only used cars. Typically, someone will sell us or part exchange a car, we hand them a bit of paper and then we sell it to someone else. I've got two tables, one for cars (each with a unique ID) and one with customers (each with a unique ID). Now, currently I have a one-to-one link as in the car's table is a field for cust…

I'm working on developing a database for habitat surverys. We collect multiple measures for each category (i.e. Substrate: silt, gravel, pebble, rock, leaves), but in the end are just interested in the average percent of the site that each type covers. To minimize error, the hope is for raw, unaveraged data to be entered into the table, and have FM do the averaging by subtype and spit that out in a master table (Habitat). Right now, the data is being entered into a portal, and the weighted average is a calculation field in the same table (sum of substrate type/number of samples taken) determined via a self-relate. So if there are 5 silt entries for one site, this ave…

I feel like there's a much better way of doing this on order to generate what seems like a simple report, but cannot work out how... always struggled with report summaries and subsummaries! SCHEMA Table A = clients Table B = invoices B is joined to A by two foreignKeys for the purpose of this relationship clients::ID = invoices::ID_client clients::REPORT.ON.YEAR (global) = invoice::year REPORT I then have a report using table A CLIENTS. (1) Requests a report year (2) Finds all active clients -- whether they have an invoice or not, thus why we need to do this from table A occurrence) (3) Shows all one row for each active clients, and th…

I know I must be stupid, but I can't figure out how to make this work. I have a set of data, and I need to display all the data from that set generated in the last two weeks (from current date). This must be done in the current record being worked on live, so it has to be through a portal, not a find. Can someone tell me how to do this? Thanks J.

I am at a crossroads in my development of a database. I began experimenting with the anchor-buoy strategy because the elegance of the layout-based approach appealed to me. But now I am realizing that this may not be the best solution for my database goals (and perhaps not as elegant as I thought it was). I hate to redesign the relationship graph but I'd hate even more to redesign it at a later date when my database has grown even more. If I understand correctly, the Anchor-Buoy approach encourages only the Anchor TO to possess a layout, while the other TOs support the the Anchor TO with related data. Again, if I understand correctly, having layouts in the…

I have a Quote table with a field on it named "_kf_ContactID_OrderedBy". On the Quote_Detail Layout I have a portal "quote_project_PROJECTCONTACTS" and within the ProjectContacts table there is a field called "Type", where the used can select from 1 of 4 types: Bill To, Ordered By, Ship To and Install To. The user can add as many project contacts as needed and designate each contact a "type" multiple times. So, a user could add two different contacts and make both "Ordered By". I want the field "_kf_ContactID_OrderedBy" on the quote table, auto set the ID of the contact if there is only one "Ordered By" type, and if there is more than one, let the user select, v…

In my current solution I have a sorting script that enables users to sort by date (for a date field) but I just can't figure out why, in a self-join relationship, I can't get the dates to sort within the portal; the sorting script works fine when queueing records to print, it's only within the portal I can't get the records to sort. I disabled the "Sort Portal Records" checkbox within the portal so that's not locking the sort into one direction or the other. Am I missing something, or...?

Hi Everybody, I am new to FM and I am stuck with this relationship. Can someone help me please? 1 client has multiple delivery addresses. 1 Address = 1 client I want to show a list limited to addresses for 1 client ID selected. 1 TO : CLIENTS Key: Client ID 1 TO: CLIENT ADDRESS Client ID Address ID (key) Examples: CLIENT ABC DELIVERY ID : 1,3,5 CLIENT FGE DELIVER ID : 7,3 Thanks a lot guys! :laugh:

I'm trying to relate an INVENTORY table to an INVOICE table using a LINE ITEM table. All records in the INVENTORY table have a unique ID number, which seems like the best field to use to base the relationship on. The problem is, the users have no need to ever see that number and I don't want it taking up space on the LINE ITEM portal on the INVOICE layout. If I DO add the ID to the portal, then I can get the other fields to autofill using a lookup. But without the field on the portal, I can't find a way to let the user select the record by choosing the INVENTORY::ItemName and have the price populate, since that's not the field I use for the relationship. And if I cha…

I want to build a multi-level tree structure with a single table, where every child has just one parent. The table is fairly simple and I call it 'nodes', it has the following fields: IDnode IDparent nodeName path (a calculation that should show the whole path from the tree root node) Root nodes will point to an IDparent valued 0. I create a second instance of this table called 'parents' and a relationship between both tables: nodes::IDparent = parents::IDnode Then I write the following calculation in the path field: If (IDparent=0; node; parents::path & ">" & nodeName) I have been able to do this with previous version…

I am working on something, and can't seem to figure out why this isn't working. My solution uses two files - the data file and the UI file. I've connected the two by adding the data file as an External Data Source (Filemaker). What I am trying to do is have my UI file go to the data file and give me the sum of matching records. However, when I use the product name field as the match, I am getting results, but if I attempt to switch to the product type or product category, I get no results. The data table is called Line Items. In it is stored (along with some other data) the line item ID#, the product id and product name, the quantity, unit cost, extended…

Hi! Just signed up in the forum because i could not find an answer to my problem in 1.5 days searching various forums and googling a lot. I attached a partial screenshot of the relationships in the database. Basically I'm looking for a method to relate ConfigItems (which are actually servers) to each other. So for example: ConfigItem 1 has a ConfigItem 2, which could have also ConfigItem 3 and ConfigItem 4. How would you design this? I tried to add another table ("CI_CI), which would keep record of the parent-CI and the child-CI. Hopefully I could explain myself properly, otherwise I'll give it another try. Thanks in advance!

I know that timetables have been asked about in the FM world but, still i can't get my head all the way around the idea or concepts. I have an excel sheet (provided) that I have been asked to convert to FM. I most gratiously accepted hoping to hone my skills and thinking that this wouldn't be that tough...boy was I wrong. In looking at the spreadsheet, i was thinking that i need tables for the sessions (twilite, matinee, evening), one table for employees, one for the duties (supervisor etc..), and i think this is where i got lost. I was able to create the database using seperate tables for the days of the week. I have it working smoothly, looks just like the excel sheet. …

Hi folks, I don't know if this is the correct place, or not, but I have Filemake 8.5 and have designed my own databases with it for years. I've always managed to find a way to do things but have now hit a brick wall. I have database 1, which contains all the main data on projects. I also have other databases linked to it so I can import full client details into the main one from the client database, etc When I do letters, I hae always been able to auto-enter items I need into such as the recipient name and address without any problem. Now, what I want to do is auto-enter items into a letter from the main database, but get the recipient address from anot…

When attempting to make a relationship between 2 tables, some fields ( 2 actually) are grey and not selectable. Why is this?

Novice FM user and now am upgrading from 8.5 to 12 because it allows for mobile applications. I want to make use of the Invoice Starter Pack to track invoicing and payments. Currently, this activity is done using EXCEL tables. The Business rules are simple, payments are applied to the oldest unpaid invoices for each account. For each account, the invoices are summed and the payments are summed and the difference is the balance. My thought is to create a RECEIPTS table (mirror of INVOICES) to capture and print the payment data and receipts. The thing I am struggling with is how to have a subset of data from INVOICES and RECEIPTS store in a single table so that I can …

Hi, I am quit new in File Maker. I have a problem regarding Table instance. I have made a script in layout it triggers upon press of a button. It suppose to update some values in another table. That tables instance is in relation to my original table. My for some unknown reason i cannot get it updated on values. Is there is special way to update values in instance ? Kindly help me solve this problem. Thanks

Hello I'm working on a solution that looks up fees for a service: the company running the database performs the service for several clients, and the fee paid for each service is set by each client and varies between clients. The rules for the fees are complex and great in number. For example, the fee is based on the postcode (zip) of the location, and the value of the item. They might look like this: Company 1, postcode 2000 to 2133, value 0 to 100, fee = $10 Company 1, postcode 2000 to 2133, value 101 to 200, fee = $15 Company 1, postcode 2000 to 2133, value >201, fee = $25 Company 2: postcode 2000 to 2133, value 0 to 200, fee = $20 Company 2…

I *think* this issue involves trying to do a look up that matches the value in one table, to a *field name* in another table (and then finds the corresponding data)… but I may be wrong. Narrative description of the problem: I have a dataset of countries that have experienced civil war, and want to be able to look up the GDP per capita for each country for the year of that country's peace agreement. The civil war dataset is my own creation, I get the GDP timeseries data from the World Bank. Naturally, the year of a peace agreement is different for each country. What I have so far: I have a Main Table, which includes fields for the country name (CName) and th…

I'm in the process of rebuilding a legacy database which went bad, at which point we found all the back-ups were bad too. This database was built pre fmp6, and has relationships set up with a whole bunch of other FMP databases, most of which have relationships which point back to this one. As it consists of some 160+ layouts, 19000+ objects and nearly 600 scripts, we took the decision to apply FMPromigrator to the rebuild. This did 80% of the donkey work for us, and after fixing the other 20%, we now have a rebuilt version which appears to be correct, or so we thought... When FMPro Migrator is used, it creates a new FMP database which contains a table called…

I'm thinking a global may be required to accomplish the folowing, but I'm baffled. Four fields in play = Start Mileage, Ending Mileage, Total miles traveled and Highest Mileage To Date field. Start and Ending fields are manually entered and result shows in Total Miles Traveled field. I'm seeking a button that captures the Highest Mileage To Date ( existing in any record ) and auto enteres it into the current record's Start Mileage field. Seems so simple, yet the creation eludes me. Solution anyone, please?

Attached are two images. The Contacts.jpg is the relationship graph where I am creating multiple TOs in order to feed their contact information into a layout from another file. The Layout.jpg is showing what I need the result to be. We enter the contact name into the layout. The company and phone number is pulled from the Contacts TO via the relationship between the contact name field and the assigned Contacts TO. I'm wondering if there's a better way. My solution is riddled with these Contact TOs. I have to recreate them over and over every time I create a new layout that needs contact data. Can I create a global field somewhere to either (1) reduce t…

I am VERY new to filemaker and playing around with filemaker pro and I need it to do the following things: 1) Store all my contacts (done, this one was easy) 2) Build a form for sample orders, (one for buttons, and one for zippers: again this is fairly easy..) a) what I can't figure out is how to be able to make a field where I can type in the customer name, filemaker searches for it in the contacts database and then populates the name and ship to and bill to addresses is there a way to use the starter solutions and add a field that will pull from the contacts database to populate customer names, bill to and ship to addresses? Thanks!!!

Is it best to try and incorporate ALL related data of a business into one file, or should different aspects of your business be divided into different files? I am looking at helping someone set up a database for an events management business. I am trying to map the following tables: clients, correspondences, event, goods-services, EventOrderLine. But is it too ambitious to try and link this with expenses, employee payroll, payments, etc. Even though some of the data is common between these tables? Just trying to avoid redundancies... I assume there is some way to share data between files? But is this recommended? Any advice would be greatly apprec…

Hi everyone, I've been scratching my head with this one for days. I have a database that has a number of tables as follows: Users Clients User Client Join Each of these have primary keys and are related by them. Now, the problem begins when looking at the 'Clients' table - or any instance thereof. I have set the privileges definition based on the users primary key (which is set as a startup script to a global $$userID). I then defined the privileges to say that they should only have access to view or edit a client if they have a record in User Client Join with their user ID. It looks something like this: cClientAllocatedTo_n = $$userID …

I have 6 tables with lots of records. I would like to merge them. Is this possible ?

I'm trying to fashion a relationship to pull together up to 20 images based on a list of Record IDs (ObjectIDs in the example). I posed a question earlier with regards to calculating the contents. Now I'm exploring how to fashion a relationship. A main point to know: the Object records must remain unchanged by this process. In other words, they can't be flagged, which rules out using a scripted Find. Besides, I already know the necessary Record IDs. My 2-image example does do the job ... but it's not easy to scale up. 20 Table Occurrences to support a 20-image photo contact sheet is not a good solution. I feel like I'm overlooking some simple feature t…

Hi guys, I'm having some confusion with my filemaker database. Essentially I have a table which has records of the customers that my company markets to. We send out flyers to our customers and I want to be able to track all of the previous dates that we have sent flyers to our customers. What I've done so far is created a new table with records for each date that I've sent out flyers and related it to the customer table via the customer unique ID. I'm just confused as to how to add multiple customers to a single sent date and also how to add multiple sent dates to a single customer. I'm having trouble finding tips on how to achieve this so any advice or di…

Hey, hope this is in the right section. I got 2 tables, Customers and Invoices.. one to many in my situation, customer can have many invoices, one invoice can only belong to one customer.. I`ve already made a primary key in both tables, customers_ID and invoices_ID. Do i put my foreign key in the "many" side of the relationship? And also I wonder if a foreign key should be just be a number or should it auto enter serial number 1 to 1 like the primary key fields?

Hi, I am a new in the FileMaker and Database Making. I am trying to relate the 3 tables but the Filemaker Doesn't allow me. Kindly help me resolve this issue. Here is how i am trying to relate the table. Purchase Purchase ID (PK) Supplier ID (FK) Date Purchase Line Purchase ID (FK) Product ID (FK) Quantity Product Product ID(PK) Product Name I am trying to relate the Purchase Detail with Product and Purchase detail. But Filemaker creat another Graph or Table (I don't really understand it why? or How?). But is that a wrong ? why? And how to resolve this in Filemaker?

I'll be as concise as I can, while seeking help to understand what the best table structure is for this scenario. A) Distributors sell my product, and so do I. I am a Distributor. Some distributors ask me to send the product to the end Customer, but they invoice them, and I bill the Distributor periodically C) Some distributors ask me to send product and bill the Customer. Then I pay them a commission. D) Some distributors buy product from me and handle their own sales. So One Distributor can have Many Customers A Customer can belong to only One Distributor, but doesn’t have too. A Customer can be a Distributor, but may not be I am…

Hi, I am new to FM and i have been building a database for my building company - to take care of a lot of the office and document control. Essentially what i have is a bunch of different tables that are children of a parent table called "Projects". A project is a single occurrence of, say a new home build. I have run into a problem where i am trying to access information from one of the child tables ("DescriptionOfWorks") and place that information into its sister table "QuoteRequests". I have attempted join tables and all soughs of tweaks to see if i can work it out myself but i just can't seem to figure it out. Could someone please take a look at the …

Hello, I dont know if this question rightly belongs to Relationships or not. I have created a database with two tables which are related by a unique ID. In the first table I have certain specified information - such as Name of the person, DOB, Unique Number etc In the second table I have information of what items were performed, Date of Performance, Unique Number Each Person may have number of dates of performances and on each of those dates - number of items will be selected (from 1 - 20 items) At the moment the database is working well as for reports I am using the primary table as the baseline and extracting data from the item table by a Portal and it s…

Hello I have data for 3 workshops, WS1, WS2 and WS3. One person can attend 1 or all 3 workshops. How can I combine the information into one report/list or something I can see who is in what workshop without going through 3 places? I use 'find' to have a list of all 3 workshop enrolments, which looks like this: Mary WS1 Inv#111 $100 Credit Card 5/6/12 Mary WS2 Inv#111 $125 Credit Card 5/6/12 Mary WS3 Inv#250 $50 Bank Transfer 9/6/12 Peter WS2 Inv#75 $125 cash 1/6/12 Peter WS3 Inv#75 $50 cash 1/6/12 I need something like this: Mary WS1 WS2 WS3 Inv#111 $275 Credit Card 6/5/12 Peter …

Hi There. I have been trying to figure out a solution for this for a day now and thought it would be best to ask. My database contains a table of plant machinery models e.g. JCB JS130 Excavator I have a separate table containing all of the "machine makes" e.g. JCB, Caterpillar, Takeuchi I also have a separate table containing all of the "machine types" e.g. Excavator, Dump Truck, Telehandler Every record in my "models" table has a reference to a "machine make" ID and a "machine type" ID. These tables are related and its all simple enough. I now have a table with one record in it, an item. This 'one' record needs to be related to 'many' models. I have 3 …

Hi, I like to create a program to keep tracks of the rental payment and I like to have the monthly receipt automatically generated when the date is near. Is there an efficient way do it other than looping through all the contract table? Thanks. Kent

I'm creating a Inventory/Payment system. I'm using the FMv12's invoices start solution. I've added the Payment portion portion that seems to work fine. The "inventory" is being tracked on the products table, with a in stock calculation field along with a field for stock adjustment. This will allow manual adjustments aside from the PO (purchases) and Invoices (sales) of the product. As i was thinking of possible things that may occur, Item returns, is one thing that stuck out. However, I'm not sure whats the best way to do this, should their be an RMA table? Any suggestions? I've attached a schema on what I have so far. Thanks invoice_system_schema.pdf

HI. I'm fairly new to Filemaker Pro, but have used it well enough to create a decent Relational DB. However, I still consider a newbie. I'm using FM12 Adv. , I'm in the process of modifying the Invoices starter solution, by adding some calculation fields and adding Payments and inventory to it. The issue I'm having is that the INVOICES::Payment Status is a simple Text field, however, I want this to be an Calculated field (ie If(Payment::Total >= Invoices::Grand Total; "Paid"; "Unpaid"). This works, however, there are 2 TO of the Invoices Table (Unpaid Invoices and Paid Invoices) these 2 TO are connected to the Customers table, to query up any invoices that are un…

Hi, How can I make a relationship that is based on a patterncount of a global field work? for example: Global = bite I need all the rows that has bite as a word or part of a word (like "snakebite"). The problem is that the field that is based on this calculation is an unsorted calc field and cannot be used in a relationship. Is there another way? (I cannot use portal filtering this time because of various technicalities) tnx :)

I want to say hi to this community. I like to introduce my self to you guys before i ask any question. ;-) So, My name is Waleed. I am from Pakistan and very new user to File Maker. I just got my first Project in File Maker Pro. So, as i have told you guys that i am new to File Maker. That is why i am facing lot of Problems. Searching through the internet for Solution i came across this community. I really liked it because it is dedicated to File Maker. Back to question! I am trying to make a database for Little business. The objective is that Supplier-(Product)->Stock-(Product)->Customer. We buy product form certain Suppliers. After we bo…

Hi everyone, I have been using filemaker for about 6 months now and have created a valuable tool for our business. I have a table containing customer accounts. I have another table containing branches. Every branch record has a related customer account record. There maybe say 8 branch records all with the same customer account ID. A record in the customer accounts table will always have a related record in the branches table with exactly the same information. The customer accounts records are used for orders and financial information whereas the branch records are used for marketing and CRM information. The only common fields between them are ad…

How can I create "subrecords' within a record? For example, I make a record for a company and amongst the various information I have for the company, I also want to list their various products, prices, etc and continue to add to it easily....so it looks like this. The only option I can find a portal but that isn't what I want. The other options is I need to make fields each time I add a new product but that isn't very efficient. So basically is there a subrecord option? Thanks! Name of company Product name Price Availability Misc 1. 2. 3. 4.

Hi, I'm trying to create a student assessment database to replace an older flat-file 'database'. Though I'm having trouble relating assessments to both subjects and students. I'm sure this could be resolved by more TOs but I'm struggling to get it to work. I'm looking to have a students grades kept for individual assessments. Assessments are related to Subjects. If someone is even able to point me in a direction I could investigate, or a similar 3-4 level relationship it would be greatly appreciated. This is the relationship graph of a mock up I designed which replicates the actual database. This is a greatly simplified version as the full version contains…