Relationships

This is how my relationship looks like now. Example file can be downloaded from HERE. Relationship works until I have categories that should be related to more than one mainline. I have two mainlines called clothing and cloth/fabric. Then I have category called Shirts. As T-shirts are fabric and clothing, it should appear and be related to both mainlines, how to make it so? Currently its only possible to join it with one mainline. Example file describes better what I am trying to achieve. Comments included. How do I make it work? Thanks in advance. Update: I added a jointable to fix the problem, but is this the only way to go? Or is t…

I am a beginner in FileMaker and would appreciate it if you could help me out with his issue I'm having: I have a "Market Profiles" database which contains two tables: Markets and Market Profiles. Market is a breakdown of the governmental departments of a given City or Town while Market Profiles would be used to store demographic information about that City or Town. In the Market Profiles layout, the user can select Country, then Province, then Region which are all filtering properly. Once I get to the Market (the City or Town), I get the entire list instead of a filtered one (based on the Region the user selects). Please see the link below for an image of th…

I am developing a custom application tracking system for my travel nursing and healthcare staffing company. The system will have modules for job orders & website, contracts management, human resources, time & billing, etc. There are custom layouts for my in-house recruiters and admin staff. I am also using CWP for the online application that individual candidates will use to register and manage their own account. Everything has gone smoothly until now and I was not able to find the solution in the FMP Bible mainly because I did not know what to look for specifically. Here is the scenario--when entering a new Candidate, they will select their profess…

I developed a database to for scheduling judges to judge events at a conference, and now I want to check if a judge has conflicts in the events they are judging. For example, I want the judge to be flagged if he is signed up to judge an event on Day 1 from 3pm-5pm and Day 1 from 4pm-6pm. Because the times overlap, I find the difficulty. I do not need a full calendaring software, just the conflicting checking of someones script. Thank you

I do not know if this is where I should post, but I am so lost. I am pretty good at relational databases. I do have FM Pro 10, but have still been using 9. I work for a school district and we are buying a new lunch program. The old lunch program I have retrieved the data from, put into Excel and brought into filemaker pro. I have used student numbers to compare to our current student information system and old lunch program so I know they match. I then use the household to put the students in using a portal. the old lunch program tracked the money by the family. The new lunch program tracks by student. No problem, except I need to find a way, if possible,…

I am working on a database to track training of staff. I have one table of all staff and their roles. I have one table of all exams and the roles required to take them. I use a join table to enter what staff have done what exams. I use portals on the staff layout to see what exams should be done and portals on the exam layout to see who should do them. All this is basic so far. What I want to do is see a list of all the exams a person is required to take and somehow see which ones they have actually taken (with something like conditional formatting) I cannot work out a relationship to make this happen. Thanks ;)

Hi, I am starting to create a simple DB that is an HR program recording all potential employees walking through my office, as well as employee performance and monitoring, including his monthly timesheet, sales, and evaluations. I am currently stuck on the latter, trying to figure out how to create these tables and their relationships to "Employees". What is the best way to record timesheets? Should i have a timesheet for each employee and every instance becomes "Day of Work of EMPLOYEE X", "EMPLOYEE X MONTHLY TIMESHEET", or should it be "THURSDAY, xx/xx/xxxx" and input all workers present that day? Then, how would be the smartest way to incorporate that into the…

I added a new file to work with my solution. I've established a relationship between the new file and a file within the solution. (solution has 5 files/ modules that all open upon opening the main file) I cannot get the new file to open as the others do when I launch the solution. What am I doing wrong? More over, what controls which files launch upon opening a files within a solution. I always thought it was the relationship. WHen I open the main menu. the related files open but not the new file which has an established relationship.

I have tables named Posting and Donations. There can be any number of Donations within a Posting. Donations represent checks identified by Donor ID, Date and Check Number. Checks are assigned to a Team Member (another table.) However, Checks can be "split" meaning shared by multiple Team members. In a split, the application creates multiple Donation records (1 for each team member sharing the donation) with amounts reduced to reflect the sharing - the Donation amounts for the same check number will always total the Check Amount of check. I want to produce a report and an extract (e.g. CSV) of the checks in a Posting that ignores any splits. My question is what'…

Greetings! I would really appreciate your suggestions on how to approach the following situation. I work for a academic grant program and I'm trying to consolidate all of the records at our offices. I currently have a table containing 1,584 grantee records all of which have been updated in the past 5 months. I have just found an Excel spreadsheet containing information for every participant we've ever had. There are a total of 1933 records in that spreadsheet. How can I merge these two databases into one consolidated table? Specifically looking for a solution that will add the missing 394 records without modifying the already existing records. I cou…

Hello and sorry for my silly question, but im very new to Filemaker Pro. My father has a shop and i make a database for stock reporting. My problem probably is simple but for me is very dificult to do it. I have the field "Previus status" this is the stock that we already have, the field "sold" where we put the sold items and the "current status" where is "Previous status - Sold". for example "500 - 100 = 400 in the filed current status. If i go to remove another 50, the result it is not 350 but 450. because take again the Previous status. How can i solve this.... AAAAGGR!! I hope to undestand my problem, also i include a screen shot. Thank you very much.

I am fairly new to FileMaker. I am a novice programmer. I apologize if I missed the solution to this problem on another post.. Here is the issue I'm having using FileMaker 9 Pro Advanced on XP. I have setup two tables. One table is called Market Profiles - a table containing information about any given city. The other is called Venue Profiles - a table containing information about hotels and such. This is what they look like: Market Profiles kMarket_ID - a numeric key generated upon the creation of each City or Town MarketName - user enters the name of the Town or City MarketProvState - user enters the name of the Province or State the City is in …

I'm your help Thank you in advance. File Maker new kullanıcısıyım.Bir file planlıyorum.Fakat how the relationship should I help when I'd like. Companies have this company out of rent from you and also your own car with my service I give the car a daily basis çalışıyorlar.burada Company and of automobiles How do I keep the accounts. Thanks Nuri :)

So I'm pretty new to FileMaker and I have a relationship graph question. A screenshot is attached to make this easier. My companies have many line items. My products have many line items. However, my products also need to have many prices, but these prices depend on which company is getting the line item. All fields in LINES need to be looked up from PRODUCTS and PRICES. How would I hook PRICES up to the relationship graph? I tried creating different TOs of Prices but I can't seem to figure out how to get LINES to properly look up PRICES dependent on the company. Thanks for any help!

Another relationship question (I think). What I’m trying to model with my graph: There will be different teams that will be selling the same products to different customers, and possibly at different prices. Customers can be put into groups, but it’s not mandatory. Here is a simplified ER Diagram: The red represents entities will be “owned” by one team and will never change teams. The green represents entities that will also be owned by one team, but could possibly change teams. I don’t want teams to be able to see any of the other teams’ records. I think I want everyone to share a product list. Prices (and costs) will depend on which team …

I am trying to make a timesheet to track time per task. Currently, I have 5 records 1 each for Mon-Fri. for Bob all within the same week. I have made a layout that displays the records in a table view like below. Nme M TU W TH F Bob 8 0 0 0 0 Bob 0 8 0 0 0 Bob 0 0 8 0 0 Bob 0 0 0 8 0 Bob 0 0 0 0 8 I would like to have the layout display like the following. Nme M TU W TH F Bob 8 8 8 8 8 I am unable to come up with a solution to do this. I have used Filemaker for a few years but am not very strong in developing. This will eventually have many records of many people in the DB which will key off of a Week Ending Date. I am unsure how to set …

Hello kinda new to filemaker, here's the question. I would like to create 4 interelated forms ie: Quote, Work Order, Shipper and Invoice how do I make it so as the data transfers from say Quote to work order when I click a buttom called quote accepted for example.

I'm pretty new to FileMaker Pro but have quite a technical background and pick things up pretty quickly. Let me lay out the scenario of what I'm trying to accomplish and I'd like to hear how you would structure it. I have several sales reps. Each of these sales reps has multiple accounts that they've signed. There are commissions associated with these accounts that they get paid. I'm trying to create a database where I can enter in personnel info for individual reps to be displayed at the top of the page. Beneath that there would be 2 or 3 tabs associated with their accounts. On one tab I would like to be able to add their accounts, one row for each account. I w…

I have 2 tables. One is called "Reps" and the other is called "Accounts". The reps table contains general info for my salespeople. My "Accounts" table contains information about accounts that the salespeople have sold. One of the fields here is "Rep Name" which is a drop down that pulls directly from the "Rep Name' field in my "Reps" table. I'd like to do 2 things: 1) I have a page with a form where I enter in all my rep info. This is on the left side of the page. I'd like to be able to put a form on the right side of the page where I can create new accounts. Ideally, it would know that the new account belongs to salesperson A because his record is up on the lef…

Hello, I am new member and would appreciate your opinion. I am trying to build a new database of product prices. I have over thirty shops with products, and want to compare prices. Each product may be in several package sizes not necessarily the same in every shop. For example I may have a chocolate in three different package sizes in shop1, four different package sizes in shop2 e.t.c. I want to be able to group each product, gathering all packages from all shops and then compare prices, by converting the other package sizes to the ones that I have in my shop, whenever they differ. Then, I would like to modify the price of product in my shop, to be the average of the th…

Hi, This is probably really easy..but.. I have one table USER STUDENT_ID FIRST NAME LAST NAME (ETC) This is connected to another table ICT via STUDENT_ID STUDENT_ID SKILL1 SKILL2 SKILL3 This works fine. So good infact the other teachers want their subject included! No problem I thought. I'll add a new table: ENGLISH STUDENT_ID SKILL1 SKILL2 SKILL3 This is also connected to USER via STUDENT_ID. The problem is: When trying to input data into ENGLISH it states that I needto create a new record. However this creates a new record and still wont allow me to inout data! Any ideas please? Jon

Hi There, I have a problem I can't figure out.... I've created a database with several tables with each table containing it's own set of unique ID's which are unique in the DB. (new tables will be added every few months) I've also created a table combining the unique ID from each table. Now I would like to add a field to this table which will lookup a field value linked to the unique ID, but this field value can be in any of the other tables. Or more specific: I'd like to add a name field to table(multiverseID master) which will lookup the multiverseID in the other tables and give the corresponding name value from the table which contains the releva…

Hi I'm new user of FM and I create 4 Tables in my DB. There are Product:kpproductid---Selldetail:kfproductid,kfsellid----Sellinvoice:kpsellid,kfcustid----Customer:custid, and I want to have a layout base on table Product which will have the sum of qty. of each product sold to each customer(sort by sum of qty). I've tried to do that but the summary field "sumproduct" that I create in product table show the total qty. which sold to all customers. It is not the figure that I want. Pls. show me how to create it. Thanks

Our company uses something called MRTG, which displays graphs of our customers bandwidth usage. Now I am getting the link from an External Data source(mysql) table in a field called "linkurl". So as our employees page through our customers, they can go to a tab called MRTG which displays the graph using the webviewer. My problem is Each of our customers have 2 graphs assigned to them (in other words there are 2 related records for "linkurl" per customer and the webviewer is only displaying 1 of them. How can I show both related records in separate webviewers? If I just simply add 2 viewers it just displays the 1st record twice (or displays the same graph twice) …

Hello Everyone, I'm new to FM Pro 10 and I'm trying to create a proper relational database. My problem is that in the KeyData Table, I can pull up the room, but I can’t pull up the Suite. It should work like this: KeyData [RoomID] -> Rooms[suiteID] -> Suites[suite] and that path should return the Suite Name in the KeyData table, but so far its only returning blanks for suites. I think I've read that FM Pro has problems grabbing field values from chained tables, is this true? and if so is there a work around? Thanks for any input.

Evening all, I am developing a database for a small freelance design business of my own and need some help on the Quote->Job->Invoice workflow and whether or not to separate line items into different tables. Basically, first comes the Quote with various Quote Line Items for each stage or work. Line item fields include: Category Description Estimated Hours Hourly Rate Markup Calculated Cost etc. With not all fields being show on the customer quotation for obvious reasons. After the quotation is approved by the client, next we have to raise a Job with the same tasks as listed in the quote (Category and Description being the same as…

I started a contact database with separate tables for companies and contacts joined by a relationship so I can add contacts to a company. This way when I update company info, I only do it once and its updated for all my contacts. I'd like to track when contacts move from company to company rather than delete them from one company and add them to another. My idea was to create a third table and place in between the contacts and companies table relationship adding fields date start and date end. Since I'm a beginner I hoped to get some feedback on whether this would be a sensible approach or if there is another way others have already had success with. Thanks…

Hi, My first post, allready found many good answers in this forum, thanx! Issue: I have a table of items in a budget. This table contains a field called "CategoryID" to categorize the items, let's say "A", "B" and so on. In my budget overview, I display the items in portals, one portal per category. My question is; can I make FM auto-enter "CategoryID" with a value corresponding to the portal used for data-entry? Example: If I want to enter an item in the "A" category, I use the "A"-portal for data entry. I want the "CategoryID" to be labeled "A" automatically. I guess there is an easy solution to this problem.

Hello I have a table of contacts and a table for business reviews. I want to link these two together so I can pull the business info from contacts into the business review table, but not sure exactly how to do this. I keep thinking in my head that a business in the review table will only have one address and one address can only be associated with one business, isn't that a one-to-one? Or is my thinking wrong? I have read many times that one-to-one's are very rarely used. I did set up a relationship using primary key in contacts to foreign key in business reviews and this shows as a one(contacts) to many(business reviews). This does seem to work, I set up the fo…

What is the best way to link the "operation company" to all tables in solution? Here's kinda what I have built: I have a table called "companies". The company operating and using the database exist in this table along with clients, vendors, etc... I also have a table called "home". On the home layout is where user assigns the "operation company" from a list of companies in the companies table. So I need to know the the best way to let all the tables know what record is the operations company record. The only way I really know how to do this is to create a calculation field in each table to equal the set operations company ID from the home table. Seems like…

Hi everybody. Apologies for this very basic question. I have made a database with the tables CUSTOMERS (parent) and ORDERS (child). Each time a customer orders a product I add a new related record to the ORDERS table via a portal. The ORDERS table contains a date field, COMPLETED_DATE, in which I enter the date an order was completed. Now I need to invoice for all the work I did in February. What I would like to see is a list of all the orders completed during February, sorted by customer. Do I use a Report for this? Or are they just for printing? Again apologies for asking such a basic question. Kind regards, Frosty.

Problem Relationships: I have three tables: Table 1: Pizza Restaurant Locations Table 2: Pizza Orders Made for each location Table 3: Menu Items(Cheese, Olive, Pepperoni) I want to build a report via a Portal on a layout for Table 1. Showing all the menu items ordered for that restaurant including the quantity totals for each menu item for that restaurant . What is the best way of doing this.?

I'm trying to produce a report for payment of royalties by a company that sells both digital and physical (CDs) music. Sales can be based on tracks (individual songs) or albums (i.e., collections of songs); royalties are paid out based on sales numbers. The problem is that the report from which the sales information is extracted doesn't directly link the albums with the songs contained on the album. The song database contains a record for each song and relevant info: kp_song_id (a number that uniquely idetifies each song or song part on which the royalties need to be paid) title artist ISRC (a number that identifies the song) UPC (a number that identifies the…

Good morning everyone. I have what is probably a very basic question to answer, but i'm a bit confused as to where to start. Basically i am developing a small db to manage freelance design work, with projects, project stages, clients (individuals or companies) and an invoice functionality. My question is: How do i automatically populate the client details in the invoice when the client could be either a company or an individual. I would like to separate companies and individuals into two different tables and not just mix everything into a single client table. For example, supposing i could figure out a way for the database to detect whether my client is an …

I am working on an older server-based database, now on FM 10. The database is set up with two separate files, a song file with info on songs and a sales file with info on individual song sales (downloads) that is imported from an online retail outlet. The sales file is 900,000 records and growing. The database was set up to use lookups from the song file to the sales file, rather than importing sales records into a table in the song database, on the the theory that so many records would bog down the database. Which is better, using lookups or keeping that many records in a single database?

I have fields in table 1 that is set to auto lookup from fields from a related table2. Any given record in table 1 can be related to several records in table 2 I want to get all the related records from table 2 in a portal in table1 but in table1-native lookup fields, how do I go about doing this? Any help will be very much appreciated! Amram Chayim Eirinberg

I have many products and each product can exist in multiple categories. I have a product table and a category table. I access the category table via a portal. I have many people adding and changing the categories so I need to find out which items have been modified to update the website. I need to have a checkbox in the products table set anytime a category is added or updated? I have used evaluate in the past but cannot seem to figure it out with a related record. If there is a better method, let me know.

Hi All, I'm fairly new to Filemaker, and this forum, so please go easy on me! I searched the forums for over an hour before posting this, and some things seemed to fit, but it's such a specialized application it's tough to get the full answer I need. Hopefully you out there in Filemaker land can help. It's a bit tough to explain so I hope I make sense: I'm creating a database to track art created for an animated TV show. For every piece of art There is one line version and then multiple color versions of the same piece of line art that need to be tracked. Each piece of art will be used multiple times throughout the course of a season and we need to not only…

The attached image shows the relationships I have established among six tables. This all works in that I can assign courses to groups and resource types to groups. What I would like to be able to do is to see all the red marked data for all courses. In other words, every single resource for each course would show up in one giant table layout. It would be ideal to see the blue marked elements as well (and I realize there will be duplicate entries in a table which is fine), but I can live without them. When I create a layout with the fields I'm interested in, I only get the first portal row for courses and just one resource record instead of dozens. Sugges…

In table 1 i have each record with two fields name1 and name2 with a different name being entered into each of them for each record entry. Each record also holds fields for date, load# pay name 1 and pay name2 plus more. In another table2 how do i get the name 1 and name 2 entries when inputed in table 1 to be put into 2 seperate records in the one field called name all thanks but i just cant work this one out as a beginner

Hi , I am developing a solution in which i am using another file that is seed code calender.in which i am able to see the appointment in calender according to date.in which what i also want is that i will be able to filter the data according to name in calender view.i have tried so many times to do so but unable to do it yet.plz help me out. Regards, John Wiliams.

I'm developing a database with medical lab reports and I'd like to generate a Report page displaying multiple lab values on one page. I'm displaying 390 different values from the Test table which are distinguished by Date and Name. However, I'd like to do this without having to create 390 relationships between the report and the data tables. More specifically, the Report layout will have 6 columns each representing a different date. Within each column there will be 65 different results, each with a different name. It would be nice if I could name the objects on the report layout and then use the name in a relationship in order to display different values in …

Hi I have my invoices table with all the usual fields: id, date, client, lineitems, totals etc I'd like to create a start page or dashboard, that along with displaying other tables, shows me my sales per month. I have managed to create summary reports that show this to me, but if I can also incorporate my sales summary into the dashboard, that'd be perfect. Details: I would like to pull 2 fields: Sales—Invoices::MonthYear & Sales—invoices::MonthlyTotals into my Dashboard table. I have tried matching a Dashboard::MonthYear field to the Sales—Invoice table into a portal, but that pulls every invoice, rather than the months only. I suppose that's …

HI all, I have been away for a few years. Now am back to useing Filemaker and jumping to FM10. I feel like I am starting all over again. Nice to see so many familiar names in the posts! So, here is my issue: I have "Clients" table. I have a "Siblings" table. Clients have siblings and SOME of these may be clients (by virtue of being minors with a parent in prison). I use a portal viewed in the "Clients" table layout to enter siblings (and create records of siblings in the "siblings" table). Based on operator/enterer decision (i.e. a minor and with the same incarcerated parent), a field, we'll call "SibClient", in the portal is checked whic…

I have problem with submit buttom in IWP status area. Can you please tell me a script to replace the submit buttom? I use a lookup field to show the values from pull down menu...but nothing happens until i hit the submit buttomm. When i choose the value from the pull down menu i want to see the lookup value to the next field.....Is it possible? Thank you

Hello all. I'm glad to have found this community - I hope to share much here, wherever I can. I've been down a long road of evaluating all the mac small business accounting software for my solo business, but nothing fit the bill. So, hopefully Filemaker to the rescue. I've used Filemaker for many years, but only in small ways for my prior employers. So, anyway, after that intro: my question. I want to keep my invoice numbers and quote numbers different (unlike most accounting apps), and didn't want each to increment when I was producing the other. So I opted for two tables, both using the same LineItems table. Am I doing that right- using two tabl…

I'm trying something new in a database that automatically compiles documents through relationships. What I already have got is a database that compiles lists of items from a another database based on a relationship, resulting in something like: [item 1 from other database] [item 2 from other database] [item 3 from other database] I've been doing this for some time and it works flawlessly. Now however i want to add a bit of information at the end of each originating from the datase i'm working in. Like this: [item 1 from other db][addition from current db] [item 2 from other db][addition from current db] [item 3 from other db][addition from curr…

How can we make the database ensure each record is unique based on the values from several fields? There is a table containing course surveys. The fields are ResponderName, CourseName, Semester, plus survey answers. In other words, the primary key is made up of ResponderName, CourseName and Semester. Say we have the following records: record 1 ResponderName: John CourseName: econ101 Semester: fall record 2 ResponderName: John CourseName: chemistry101 Semester: fall How can the database be made to prevent the entry of a third record in which the primary key is a duplicate of one of the first two? Thanks.

Hi! I have a question that I want to know if anyone has solved. I have one table that has information on patients and another table that contains information their medications. In the patient's table I want to list the related medications in a "med, med, med, ..." type list for an emergency sheet that we print off. I know how to display all of the meds in columns in a portal (that is how we enter the meds), but can I have them summarized in a comma list form? Maybe a summary field that summarizes the content (text) of all related fields? If possible I would like to avoid needing a script that would go through each med in the med table and then copy the names int…

Hi, I have recently started using FM10 after using FM6 for about the last year. I had a FM6 database that related to three other FM6 databases and the relationships worked fine. I have converted this to FM10 and incorporated all previous databases as tables and set up the appropriate relationships (or so I think ;-)) Rather than go into great detail about what isn't working and why, would someone be kind enough to allow me to email the database to them to take a quick look? I'm sure I'm missing something quite small and it's driving me mad! It's basically an exam database that draws questions from one table and candidate details from another and creates rec…

I've posted here because I'm wondering if I have some circular relationships or definitions that are causing the crash (and how can I find out?) I was amazed to find virtually nothing, anywhere, on EXC_BAD_ACCESS (SIGBUS). I have a db which causes FM to crash consistently on first action after launch, regardless: menu click, button select, etc. FM works fine with other db's. This occurred occasionally before and seemingly randomly. There was a time when duplicating the doc and relaunching bought me some use; that was never a solution but was a workaround. No longer. Now it's a unavoidable. The db is quite small, 8 tables, <25 fields each, <75 record…

Please help! I have a database of maintenance buildings, with a record of all reports about problems that need to be solved. As we have crossed the figure of 6000 records it would be good to have a history of all the records sorted in some kind of a table according to entered criteria and shown immediately. For example, if someone reports a problem, we write the address down and as we do that it would be great to immediately see a list of all reports from the specified street. Furthermore, when entering the house number of the building the report came from, to show us just the reports from that address. If someone has a solution, I would be grateful.

I have a basic table called "Daily Staffing" which has a date, and lists employee's names (with a relationship to table "employees"), the hours they worked, the employee's total pay, and the total amount paid to all employee's that day. So it looks like: Date: Employee Name: Hours: Payrate: Amount: Employee Name 2: Hours2: Payrate2: Amount2:: Employee Name 3: Hours 3: etc. etc. So the calculation should be simple: Amount= hours x payrate, Amount2=Hours2 x payrate 2 etc. But I would like pay-rate field to be pulled automatically from the "employee" table. Instead of manually entering it as above. Another problem I've run into, which is somew…

Can someone help point me in the right direction. I need to create a quick system in FM6. I have a table of people with 100 records in. I also have a table of Sites with 20 records in. What I am trying to do is have a table that has a record for every site and every person. so 100 x 20. My issue is that if I add a person it needs to add a record for each of the sites. Likewise if I add a site it needs to add a record for each person. Can anyone give me any pointers

I would like to create relationships between an account and other accounts. I have created a table called role. Assuming that the best way is a join table. Plus I would like to give a description of the relationship. Now I am just not sure how to have the portal view the information properly. So if accountA has a relationship with AccountB and AccountC. I should see A in B and C's portal, and B and C in A's portal. Any suggestions.

I've recently been struggling with a database solution to track calls and pricing information. I'll try to briefly sum it up: I have 3 main tables; a, b, and c. Table "a" represents a geographic location (usually a city) that my users will be contacting business entities within. Table "b" represents the business entity that the user is contacting. And table "c" represents pricing information that was sent to the business entity. The way that my relationships currently work is that entity "c" relies on entity "b" and entity "b" relies on entity "a". Entity "a" is used to tally and track a report for all of the calls/pricing information being sent in one area…

Hi, I have received an assignment to repair a 10 years old FM solution made by someone who didn't have a clue of what was relationnal database... I attached the PDF representing the form. I know it's in french, but the question here, is what would be the best practice to create the tables and relationship for this kind of checklist? The form has not change for the last 15 years since it is governed by a superior entity. example.pdf

Hi - I'm not sure if this is the right forum for this question, but I couldn't find an obvious candidate. I'm sure someone will move it if there's a better choice. I'm trying to figure out a good system to use for documenting my database. The graphical relationship diagram is not the easiest to use for documenting how things are set up. I've tried to use background "notes" with text to describe branches, etc - but that doesn't allow for a lot of detail in describing how things are set up. I'm wondering if anyone has used any other tools for this purpose (eg Visio. DB Designer, MySql, etc) or could suggest approaches or links to threads on the topic? (I did se…

Can anyone tell me if it is possible to create a summary of a field in a database where the field is linked from another database. I basically want to be able to create a running total in the grand summary area but am struggling with the field in question being from another database. I can get it to total for one record but I want to get the grand total for all the records searched. I have had to create the field using a calculation but as I say it isn't giving me a running total just a total for the one record. I hope that made sense and someone smart can help me. Many thanks Jo

I have a General Ledger Table. Record 1 is Cash, Account #: 1001000. It has Fields: Account Beginning Balance Debits Credits Ending Balance The Debits are in another table. The detail debits for 1001000 could be 1 record or 20. That table's structure is: Account Debit Amount Credit Amount How would I get the sum of the Debit Amount in table 2 represented as 1 number (the Debits field) in table 1? For example, if table 2 is: 1001000 500 0 1001000 250 0 1103000 800 0 1103000 0 1000 ...I would like to get 750 into the Debits field on Table 1. I have created a relationship between the two tables via Account #.…

Please forgive me if this is posted in the wrong spot. I'm somewhere between a beginner/intermediate Access developer (I have a strong understanding of relational database design). I'm creating a filemaker database, and I'm thinking there MUST be some book or pdf out there targeted for folks just like me, that would describe some of the differences between filemaker and Access. (YES, I Have bought the Filemaker 10 book... The Missing Manual, but that really isn't filling the void I see). Thanks in advance for any links and comments!

I've been trying to figure this out every which way, but am getting nowhere fast. [NOTE: I've attached a PDF with a simple illustration of what I am trying to do.] I have a Main Record table. That table is holding, say, Recipes (for arguments sake). Related to that Recipes table are a whole bunch of Contacts. Easy. Each of those Contacts has a "relationship" with the main Recipe. Employee, accounting, legal, etc. These relationships are determined by a third table - Contact Relationship. Still very straight forward. Within the Contact Relationship values, some of these relationships can be flagged as "Primary Contacts", in that we can set up that …

I need a little help setting up the relationships for a multilingual glossary where a record for a given word might have the same definition across the different languages, but the example for each is unique for each language. The design is so simple that I provide descriptions here in lieu of an attached file, with explanations of table names, fields, and relationships in parathesis. Glossary (main glossary table) Esp (field related to other field of same name of table EspGlos) Fra (field related to other field of same name of table FraGlos) Por (field related to other field of same name of table FraGlos) EspGlos (Spanish glossary table) EspId (uni…

Somehow I can't quite wrap my head around this one. I have People affiliated with Organizations via a join table for a many to many relationship. I have a field "status" that is calculated by criteria as follows: Case ( not IsEmpty (date_deceased); "Deceased"; not IsEmpty (transferred_out_date); "Transferred"; not IsEmpty (date_retired); "Retired"; not IsEmpty (PEOPLE_j_organizations:_kp_organization_join) and IsEmpty (PEOPLE_j_organizations::date_end); "Active"; "Inactive" ) I want status to read "active" if ANY of the joined affiliations does not have an end date. How do I do this, or is there a better way to make this happen?

I am not sure if I am missing something obvious but this situation has been popping up frequently lately. I try to delete a record and it comes up with a message that says "This operation cannot be performed because one or more required related tables are missing. It seems as though this has just started happenning lately in multiple areas where I have never encountered any problems deleting before. If anyone can shine a light on what this is all about it would be greatly appreciated. Thanks

Hello everyone, please help. I have a one to many structure set up, which looks like this: Customer has multiple communities, communities have multiple job types, job types have multiple jobs, jobs have multiple products. On the first layout the customer selects the communities from a checkbox value list (List view, long list). The IDs get stored in a text field (Selected IDs), which relates to the Community ID, then they GTRR and on the next layout they see the communities they have selected. And so on until they get to the products. That all works fine for one customer, but as the Selected IDs field is not a global (can't use one because it will have o…

Hello again, I have 2 tables that share a field called dbnum for database number, which in table 1 is a unique number but for table 2 is a number with multiple records with different variations. The tables are a 2 step process to track if an action had been done or not. The first table is to prep the action with the unique number. I have another field called Status with the radio buttons "done", "not done", and "redo". I don't want to set the field with a calculation so that I can manually set the radio buttons if I want. When adding records to the first table, the default is to mark them "not done". Step 2 is when I'm importing a report of the action being done i…

I have a database in which I am importing invoices from several different companies. The data is named different on each invoice, however they all have about 5 common data rows. I need to be able to summarize and add up all of the invoices while maintaining the data that is imported. Is there a way to create another table which will allow me to another table which brings in the 5 data rows so that I can make accurate reports? I know this is a little bit confusing I can tell you that the companies all have an agent number and the connect to an agent database relationally. I am fairly new to filemaker so please go easy on me.

Hi, Is there an obscure trick to know if a parent table is used by children table? I know that I could add all the children tables to the parent main TO and add a calculated field in the parent table. But again, I will need to modify the calculation each time I add a new child. Eg A person table which is used in many TOs. A person as an employee, as a boss, as a buyer, ...

I'm hoping someone can help me out here. I can't seem to wrap my head around this relational concept. I'm trying to setup a db to record the positions and radios a group of people will have for each day. There are approximately 50 people, each assigned their own radio, but at times they may use another radio because theirs is OOS. Only 10-20 work a given day (shift). There are up to 20 positions to be filled with a minimum of 10 filled every day. Each person could conceivably work in any position. I need to track which person works in which position and with which radio everyday (or every shift, but that's not important right now). I know that there shou…

Hi all, I'm trying to implement a keywords/tags system, but I'm having a bit of trouble and found very little resources on the subject. I have an Items table, and I want to be able to assign keywords to the Items, with the prospect of searching on keywords afterwards. My first thought was to create a separate Keywords table, with a join-table to the Items table (as to avoid redundancy). So my fields would be as follows: Items_table: - kp_Item_ID - Other fields Keywords_table: - kp_Keyword_ID - Keyword ItemsKeywords_table: - kp_ItemsKeyword_ID - kf_ItemID - kf_Keywords_ID Does this seems like a practical way to set up t…

I'm very new to Filemaker, but up until now I have found the program quite intuitive. Here's my dilemma: I am trying to automate the process of adding contact info for artists when we create a new event (concert) There are two layouts involved the "events" layout which has details of the event including the artists, the time, the date, the program, tickets sold..etc. There is also an Artists Layout which gives the details of the artist:Contact Info, Myspace Page, Photos, etc. Here's what I'm trying to do. When an artist name is entered in the "headliner" field on the events layout, if the user clicks a button it will activate a script that creates a new reco…

(See Attached Relationship Image) I wasn't quite sure what to call this problem, but I have a task tracking application I'm creating and I have a field that doesn't seem to be displaying correctly on a layout. I have a Task table which is related to a Status table (Task::Task Id = Status::Task Id) which is then related to a Contact Table (Status::Follow Up Contact Id = Contact::Contact Id). The Status table is a child to both Task and Contact tables. So when the status is changed for a task, a new record is created in the Status table and the Follow Up contact ID may be entered or not in the Status Table. When looking at the Status Layout, the Contact…

Hi there, Just a general question. We have 39 tables in our solution in the Engineering department. One of those tables is what we call the "job log" which stores the customers name, project name, city, state, country, etc... all assigned to a job number such as 1001-123. We use this same job number in many of our calcs to simply bring in the data so we cut down on typos. In the relationship graph we can place a table occurance int ehre and label it "Job log" to associate it with the table. If we link that TO to all the other modules tha use it, it can have what they say is a "spidering" effect.Many link lines goign all over the place. No matter how well you org…

Hi. I am a newbie, so please go easy if I asked any silly question. I have a question: I am creating 2 tables: customer table ( customer_id, firstname, lastname, mobile, email, and notes) and company table (company_id, companyname, website, address, province, zip and country). I am creating a single layout to include a entry form with a section for customer and section for company. I am having problems, when I am trying to assign an existing company name to a new customer ..Or, creating a new company for a new customer. I can not get to set the company id to right case for each of them. if existing company, no need the auto -entry company_id , if no existing, the…

Hi My FM10 Application ist separated into two Modules: the application module itself with all the tables and the data and a print module that contains all the logic und layouts for printing (list of invoices and single invoices or clientslist, among other things). I ship runtime versions to my (few) clients. That allows me to change printlayouts and to redistribute the printmodule (that contains no customer data) without touching the application runtime file - no hassle with re-importing the customers data. Works well and makes life easier if change requests for printlayouts come in. The printmodule just contains TO's to the tables in the application m…

Hi All, I seriously couldn't come up with a better way of describing the type of relationship I am looking for. The scenario is as follows: 2 tables: table 1 - students (sname, enrolled course) table 2 - courses (cname, cstartdate, enrolled students) What does my relationship model have to look like to be able to: A) have a student layout, one record being a single student. A portal to all courses that student has enrolled in. : have a courses layout, one record being a single course. A portal to all students that are enrolled in that course. Any help greatly appreciated, Mat

Hi there, This is my problem: I have 2 contact databases (CLIENTS and AGENCIES). Each DB contains pretty much the same fields (name, last name, address, city, postcode...). I need to compare these 2 DBs so that I can find any duplicate contact details and remove them. The favorite option would be compare AGENCIES to CLIENTS and remove any duplicate from the AGENCIES DB. Please let me know if this is clear as mud. Thanks NikBe

I've gone in too deep and have two requests for help. 1.) I do backup my database each time I make a change. However, this time, in my rush to fix potential issues, I have added tables and added/subtracted relationships. At this point, I have completely confused myself. From your point of view, what is the best method of systematically deconstructing and reconstructing relationships. 2.) I do have an older version, but I am stuck on an obvious relationship issue: I have 4 tables: Employees, Assets, Transactions and Line Items. Key fields so far... Employees:ID#::Transaction:ID#, Assets:Item#::Line Items:Item#. I have a layout within Assets that shows…

I am setting up a client database and have the address fields set up as follows. Here is one example of issues I am having: ADDRESS TABLE pk_AddressKey City State Country Region CLIENTTABLE fk_InvoiceCity InvAddress1 InvZip Lkp_InvCity Lkp_InvState Lkp_InvCountry Lkp_InvRegion fk_ShipCity ShipAddress1 ShipZip Lkp_ShpCity Lkp_ShpState Lkp_ShpCountry Lkp_ShpRegion The reason I have it set up this way is that there are several people using the database, and want to ensure that we won't have several ways of inputting the same city. So the idea is that for both the invoicing and shipping information, we input the prima…

I have been pondering this question on and off for a bit now and have decided to get a consensus. I use relational database design and I like to normalize. Filemaker seems to do a better job of only using memory when there is something to store, better than other DBE’s that is. So I am wondering if there is any benefit to creating a 1-1 relationship in Filemaker. For example I have a system where records are created and the user can opt to include an attachment or not, it’s not required, do I just add a container to the main table or create a 1-1 relationship to another table which stores the attachments. Habit has me creating a separate table since not every record …

I'm new at this but what I want to do is be able to find data in a table for instance which clients need work done then export that find to a new table (work orders) that creates and tracks work orders, sequentially numbering them, using the client profile from the client file. IE name, address. is this a lookup? portal? what? as time goes the work order file would compile all the work done for each client.

As part of a startup routine I have a script that sets values, using the Set Field step, to certain global fields across all the tables of a database (e.g. Constants, etc) which has been working fine since I built it, but I recently added a new table and added a new set field step to this script and now when the script runs it is showing Error Message 103 and a dialog with the text "This operation could not be completed because the target is not part of a related table". I can't initially see why there has to be a relationship in this instance as the fields should be being populated in the background. Can anyone explain the logic here?

Hello guys. Need to get a Hierarchy Portal working in a multi user server environment. I've started off with this very helpful file: Excelisys Hierachy Basically I want to have a ACL (Access Control List [just a simple table]) giving me all the access rights for a user. Every user is given individual access rights. I need to get the Portal to show me only the records that appear in that user’s ACL. Because the same items can appear in many ACLs, any duplicates should be removed. Something like this: User ---> ACL ---> MenuItems <--- Scripts Layout "User" is where the Portal (Menu) is. Any advice or pointers would greatly be appre…

Hi all, I'm new to these forums (although I've read them quite a bit). I've been trying to put together an appropriate data schema for a new database I am creating, I thought this would be a good place to ask you guys for advice. Basically it will be a library of digitized "stuff", from audio recordings to scanned old photographs and documents, to old VHS tapes. The basic functionality should be for the end-user to type a word, say "hat", and have a list of results returned that all contain "hat" as a keyword. The search results should be categorized ("video", "audio", etc..). 1. What should the database schema look like? I've though about creating a …

Hi once again, I'm stalled at this point of my database and would appreciate any help. I have looked at many examples and walked through J. Starks recursive database info but I still can't seem to get a grip on how to do this for my situtation. I have an Assembly Table, AssemblyLineItems table and a RawMtrls Table. My layout is based on the Assembly Table and I have a portal for the assembly line items table. Basically, the assembly table is for a sales rep to be able to create a new "Sign Type" which can be made up of many raw materials and come up with a price. This document would also become the basis for a Bill of Materials to create such sign type. J. Stark…

There is probably a simple solution for this but it is not coming to me at this time. I would like to have portal to a table that will display the records based on 2 criteria set by the user. Example Table 1 Car Model Color Selection (red, green, blue, all) Table 2 Car Model Color Year In the main table the user would navigate to the record with the desired car model and then select the desired color [color:red]or All Available Colors. The portal on Table 1 would then display the available cars from Table 2. No problem setting this up for the model and any one of the color options, but I am having a problem determining how to also o…

I upload a jpg this one 290K... can't remember if it take a while for apporval... then uploaded again... same error here ? First, thanks again to everyone answering our sometimes stupid, unclear questions... even trying to explain some of this.. Your amazing I am in this project after running out of money paying programmers and loosing income in this economy... but stuck to finish this somehow.. and because of this... i am learning a whole lot.. but not enough sometime.. FM certainly has some strange ways.. 5 days.. 9am to midnight straight.. and close... When adding portal records of different kinds of inventory my valuelist from selecte…

I have a complex report that I cannot get to display correctly. I have a set of records in a Line Items table. Each record contains - PayerID - ProductID (based on Product Table) - Date of Product Purchase - Quarter of Product Purchase I have a Payer Table. Each record contains - PayerID I want to create a layout in the Payer Table, which displays a portal summarizing Line Items data by Quarter. (How many transactions has the Payer ID made by quarter?) e.g. Q109, 30 transactions (or 30 Line Items records) Q208, 20 transactions The only way I have been able to do this is is by creating a Quarters table with a record for each Qua…

Hi, I want to represent a structure of sold houses with their buyers and sellers. A building can be sold to buyers (Eg A couple) A building can be sold by sellers (Eg : Brothers) A buyer or seller has an address and is represented by a person. I have already done the structure to represent sold houses with buyers and sellers, but I was still wandering how other DBAs would develop the structure. Here's my structure : The structure works well, but there's a small problem. The sellers and buyers table have exactly the same structure, it's more like a maintenance problem. Is it possible to use a single table for both entities?

I'm very new to FMP and need help figuring out how to enter multiple new records in a portal without overwriting the first one. This is for a fashion designer's production management process. There are 3 databases: Styles, Materials and Orders. Each garment style has between 2-8 fabrics/trims that she would like to enter in the database that she is using to cost all parts of each garment (style). She wants to add the fabrics (materials) to the style instead of going into the materials database. If the designer can enter the fabric info into a portal in the style database, so that it will create records in the materials database, this will follow her creative pr…

Hi there, I am new to Filemaker and I'm preparing to build my first database in Filemaker, having worked with MS Access in the past. If I have a table with Contacts that is used by 3 different departments and a Contact can only belong to one if the departments (there is a field assigning it to that department). How can I make a layout that only shows contacts from that department. If I use a find script on loading the layout, I will get the correct records, however the person can then use 'show all records' and see contacts from other departments, and we don't want that. I have been using TO's to filter portals, and thats working great. I must be doing some…

I don't know what to call what I want to accomplish so my searching isn't working. I have job -> check out sheet - > check out sheet line items. Not every job has a checkout sheet. So what I am trying to do is have a layout that I can show all the jobs that have checkout sheet but don't want to show any job that doesn't have anything at all. So if I have 5 jobs that have checkout sheets and 4950 jobs that don't I don't want to have to scroll through them all to find the 5. Then on the 5 remaining I want to see every line item that was checked out (I am assuming some kind of portal for that) In the non-Filemaker world I would just create …

I use FMpro10 Advanced on Windows XP Prof 2002. I'm a "Junior" programmer - so far, have mostly designed donor management d-bases for community groups (relationships - one to many). Not as familiar with many to many relationships, but learning with the help of FM Training Series. My challenge: to be able to do statistical analysis, between any two dates (i.e. monthly or yearly reports) of the results of 3 separate questionnaires. The problem is, some of the questions have multivalued attributes, and the user can select more than one answer. I need to analyse the results for each of the values selected for that question. I therefore cannot use straightfoward subsumma…

If this is answered somewhere already, please let me know, thanks. For now I have only 5 tables in my solution; tblContactIDs, tblPersons, tblOrganizations, tblVenues, and tblContacts_Related. The tblContactIDs table has only two fields. The first field is ID_Contact that controls the creation of assigning a serial number to each entry. The second field is Type_Contact which has three values; Person, Organization, Venue How do I build my solution, such that, when I create a new record in tblContacts and assign a contact type from the value list it opens the appropriate layout as it creates a new record in that layout's table with the newly created ID_…

Hello, I have a table of a bunch of video production project records. Each record has the fields: -Date -Client -Project Name -Project Number -Producer -Cost I am wanting to create another table that I can input a month/year into a text field at the top, and generate a report of all the records from the video production table for that month/year. Anyone know a way to do this? Thanks!

I have set up a relationship between three tables on the field 'Name'. The two related tables are set up as portals. The problem occurs when I modify the contents of the 'Name' field in a record--the portal data disappears. If I change the name back to the original name, the portal data reappears. I don't know if it's a problem with the way I set up the relationship, or if there's an option that should be checked/unchecked? Any help appreciated.

For my layouts, I have a sidebar with the navigation. Now I would like to show some lists but with Filemaker 10 it's impossible to have a fixed sidebar like the header or footer. So I have to use the form view to show the list but how? A portal which points on the same table can't work. Maybe I could create a new table but I don't know how to set the relationship Thanks for the help by ChiSao

Hi all, I'm a real newbie and I would really appreciate your help. I have "produced" a database to record students assessment progress. I have 3 tables. The first: USERS Student_ID First name Last name etc The second: ASSESSMENT Assessment_ID Student_ID Date The third SKILLS: Assessment_ID Skill 1 Skill 2 etc I then produced a layout that pulled that information together. LAYOUT OVERVIEW Student_ID First name Last Name (from the user table) Skill 1 Skill 2 ( from the skill table) I produced a dashboard. The teacher can add users and these appear on the users area! no problem! From the dashboard the…

Hi, I've been trying to get this to work now with no luck so far. I have a contacts File where I have a text field called "AccessLevel", here can be entered: "All, BOD, Management, Sales, Production, …" Now I want to be able to e.g. Get (PrivilegeSetName) = fmuser to only see "All", but I can't seem to set up the correct relationship/filter for this?