Relationships

I've been developing and working with my solution for what seems like forever so getting around and manipulating data is easy for me. but... My solution is used in an IT department to inventory all equipment and track trouble tickets. Everything is based on people, devices and services being a numbers. Basically I’m using auto-generated keys/id in the solution to assign related records/info. I’m finding that other struggle with this concept I'm looking for suggestion (examples are great) on how to maybe keep these numbers hidden but and let other use words and text to manipulate related data.. My first thought is to use more value lists but that could re…

I'm stumped on coming up with a way to change the dates in 2 global variable tables. Table1 has fields called gStartDate and gEndDate These 2 fields help define the range of records I want to relate to from Table2 and Table3. That's not where the problem is. That's working fine. I want Table4 to hold the user entered variables to define the various reports and layouts, etc. I want to be able to enter a date in a field in Table4 -- Table4::StartDate -- and have that date go through to all the records in Table1 and populate (?) Table1::gStartDate. Now, I can go into the tables and change the values of the global variables but I really don't want my users…

My boss thinks there is a way - I'm thinking not the way he is thinking. Here's the situation. We have many databases in our filemaker pro 8 system. One of them is invetory. We have a database in our invetory section that holds data related to our own part numbers and whatnot. There is another database that contains the manufacturer's part numbers and info. The manufacturer's database is used for referance regarding the compatibilty of parts in various systems. Our invetory uses partnumbers like say X1011B and in the manufacturer database, they have many differant possible numbers for that part they may have SK-1011B or SP-1011B or …

Sigh...I feel like I've been through the wringer! I am using FM Pro 5.5 -- have been trying to get my head around this for a while, and I'm really just beaten down I am essentially converting over a massive (40mb) Excel spreadsheet into database form. This database contains all the information for my boardgaming group. I believe I need one db with games (that are owned); one for players; one for sessions. Each of these is set up already, and each has a unique id code (each one starting at "1" and growing incrementally). From there, the sessions db will allow me to show which players attended. This way, I can track each individual players' attendance, a…

This should be easy, but I can't figure out why this won't work. 2 Database [color:orange]Database1 Manufacture_ID (serial, unique) Company Address Database2 Manufacture_ID Company [color:green]All I want to do is be able to have a drop down manu for Company and have the Manufacture_ID from database1 be added to database2 Of course database1 Manufacture_ID is uniuqe for ID perposes. [color:green]How would I do this? Thanks in advance.

I am pretty sure that this is just the nature of the information flow in filemaker (perhaps even just the nature of time itself...), but I was wondering if anyone had found a non plugin way around this. Basically, I am trying to get a freshly created line item in an invoice to recognize itself through a selfjoin before being committed. The attached file is an experiment with a different kind of conditional value list that works fine for items that have already been comitted once, but does not work for new ones. I have also run into this 'problem' in other contexts. Thanks, -Raz *Whoops, minor mistake in first file, reposted new one. Conditional…

Hello everyone.. First, thank you all for sharing your knowledge in this forum. Now my issue. I need to have a running total of my powder coat powder. I need to be able to have a starting amount then as it's being used, i have to be able to subtract the used amount from the start and carry that modified amount over for my next project. I know this is basic, but i just can't seem to figure this out. Yes, i'm very much a Newbie but i'm giving it my best. I have 3 tables, 1 for Customers, 1 for Jobs and 1 for the Powders. I can get the Customers on the Job layout (layout #4) and the Powder, but i can't seem to figure out how to get the running powder totals to calculate corr…

Could someone explain the following: if a join table is created with two relationships to parent tables, should both relationships be allowed to create records in the join table. Or is it just one relationship that should be allowed to create records - if so which one. Thank you.

Hi there, Thanks in advance to take a look at my problem I want to create a tabel called ARTICLES. Every article in the database must be categorized by three levels. For example Category1 Category2 Category3 If the article is added to the database, it goes like this: Category1: Wood Category2: Birch Category3: Plate Article name: art01 I want to make the database relate to the three categories, so that the user can select category1, in the example wood. If he selects category2 he should see only the subcategories of wood, not for lets say iron articles. I know I need three tables for the categories, and relate them to eac…

Hi, I am a newby so please forgive. I have one field called ‘contact’ which has both Christian and Surname, and want to show the Christian name only, as a separate field on the same form. If I use calc for the Christian name it works fine for a new record, but not for existing records. How can I update all the records? Many thanks in advance.

Hi All I have a problem with FM8 that when it's used as a host (i.e. without Filemaker server) it often opens up several windows of the same file at the same time which causes problems trying to modify records in one window as another one is open.. it has also caused me problems running scripts where a search is made in one window and another file is meant to import that data and ends up importing ALL records in the file - which i think is happening because it's importing from the other window of that same database where the search is not performed.. I can't understand why they added that feature in the first place, it has only caused me grief so far, I can't see the…

I want to create a separate table for search terms...avoiding using the built in find mode. When the button clicks...it sets fields (search fields) equal to the fields that they would normally look in. I think it makes more sense to make a separate table...but I haven't had to make a separate table in FileMaker yet...and am unsure of the relationships...need for portals, etc. Any help? Thanks.

I've implemented a TO-based method (see example file) for extracting date-relative information from a table. It works fine, but I'm thinking there must be a better way that doesn't require so much "infrastructure" -- or is this just a good use of FMP's features? It would be rather cumbersome to scale my approach upward, say to a 10-day period instead of four. Yet my application doesn't need a full-scale scheduling module. In fact, extracted days need to be viewed on different layouts (planning, materials, production, etc.), not as a master schedule. Any thoughts and suggestions will be appreciated. ALSO, can anyone tell me why the pop-up calendar in my exam…

Hi All, I'm working with the Contacts and Purchase Order templates that came with FM7. Is there anyway to import the tables into one database instead of creating relationships between two separate databases? I'd like to web enable some of the functionality and am *guessing* that having all the tables in the same database will give me more options on the web side. Of course, i could be wrong about that. Thanks for your help, Lynn

So I know what I did was a huge mistake. I made my primary key for my database as the project name, which often changes! So when I go to change the project name, then I lose the data from the other related tables. Is there way I can fix this? Project_Management.pdf

I dont know if this can be done.. Im using Fm8 Advanced I have a file with 1500 records.. For simplicity.. Carrier_rates file. I have Carier Code Field which is a number Specialty code field which is a number Then I have another 6 fields A_limit B_Limit C_limit Then A_Rate B_Rate C_Rate From another file called access_rates with a relationship I can get to the correct record based on the carrier code and specialty code but I need to choose the rate (a_rate,b_rate or c_rate) based on the Limit (a_limit,b_limit or c_limit) that is in a dropdown box in the access rates file. Is this possible.. Thanks for any help with this …

Hi all. The setup: Access a customer record on a layout that is set to display records from the Customers table. Decide to create an order: go to another layout, set to display records from Orders. (Orders is related to Customers through the pk_Cust_ID). The instant I do this, the Customers::pk_Cust_ID field goes BLANK!!! (I'm tracking that field in Data Viewer). So of course, the order gets created, pointing to a blank, non-existing, customer!!! How come I'm not having access to the customer record, just by switching layouts??? [up to last night, I was using my own logic (storing field info into global variables, then using those to popu…

Scenario: One employee can have many grants. One employee can have many gifts One employee can have many subcontracts. I have 4 tables: Employees, Grants, Gifts, Subcontracts. (Please see attachment) Each Grant, Gift and Subcontract have a unique account #. -All Grants start with an account# starting in 1. -All Gifts start with an account# starting in 2. -All Subcontrats start with an account# starting in 3. Problem and Question: 1. If I change the employees id, first name, or last name in the employee table, the grants, gifts, and subcontract table does not update the new employees name. How can I get it to do this throughout the …

Is there a problem with having two unique primary keys in the same table and layout? I want to track two different types of customers in a single customer table rather than making two separate tables. Is this unusual and am I asking for problems? Thanks

Hello, we have 2 files "Contacts" and "Invoices", related through a CUSTID field. This field is also used to display all the client details in a file called "Orders" Within invoices we have many entries for our clients, some of whom run late with payments. We have created a calc field in "Invoices" to identify the invoices that are overdue and want to display the calc field in the Client's record in "Contacts". It will also be displayed in "Orders". Where a client has 1 invoice only in "Invoices", this works OK, however if a client has more than 1 invoice, some overdue and some not overdue, we cannot get the calc field to display in the corresponding record…

I have two databases! "A" and "B" Their not realy related but I need database "B" to be able to see when new records are created in "A". I would like for Data Base "B" to show how many records are new in "A" and then allow them to import some of the data. My thought was to make a field in "A" called "import" and give it a auto value of 0. Then have "B" find the records in "A" that have a value of 0. Then allow them to import certain data from the "A" record, upon import it would change the value in "A" for that record to 1. Not sure how to make this happen or if I'm even thinking this is the right direction. Any help in making this work or a better …

Hi everyone, I have an attitude problem. We have 80,000 invoices with 500,000 LineItems that are posted and frozen and will NEVER change. This represents 750MB+ of 850MB file (our entire solution is currently in this one file). But every day ... every single day ... our office is brought to standstill because it backs up this file on our FM Server. And it bothers me. We get the dreaded coffee cup and it brings our office to a standstill. We will hit 800,000 LineItems in another 6 months. I'm beginning restructure (on paper) for re-write in vs. 8. I had planned to place all data (including Invoices/LineItems) in a separate file (separation model). But that …

Soren was a huge help with my previous problem. I'm now able to sum records from one table and display them as a value in another table. I have the client table (#1) and the revenue table (#2). I can sum the revenue from #2 into #1. I can create a found set in #2. I cannot, however, have the value of the found set in #2 be the value that is returned to #1. And, since Soren's solution was exceedingly simple and showed that I had had a tremendous brain cramp somewhere along the way, I'm sure this will be just as easy. But, I've been going through help, the posts here and I also bought the Functions and Scripts Desk Reference. Only partial luck. …

Hi everyone, Thanks for helping me out with this one... O.K. I have two fmp databases. 1. Software: Contains all information regarding software titles. Each software title has a unique ID listed in the field (software id). 2. Client: Contains all of the client cpu information. Each client has a unique ID listed in the field (client id). In the software database, what I would like to do is create a relationship where I can list all of the clients from the client database who are using a specific software on the same record as the software title. In the client database, I would like to list in each client record, all of the software titles the …

I’m in the process of converting over a large (40mb) Excel spreadsheet into a FM database. This is the data for my boardgaming group and all related stats. I found – online – this program (http://www.goldraven.com/GameTracker/GT.html). However (1) it hasn’t been updated in 3-5 years; (2) it’s not being supported any longer. It does – probably 85% of what I want to do; but not everything. It’s created in Filemaker 5.5 (which I have) HOWEVER, it’s been converted into its own freestanding .exe, so everything I could look at – layouts, define fields, scripts – is all hidden. The program is free (as will mine be…for personal use). I’ve written to the author; but my e…

Hello all! Kudos in advance to all who respond to this. I've read some breathtakingly advanced answers so far... And apparently I'm not quite advanced enough to understand them. I'm pretty sure this is an easy question, but my brain is at it's limit today. I am a complete newbie in FMPro and my Access days are long gone. I supposed to be creating a database for our company to track group and individual enrollment in a variety of of health-related programs that we sell. We currently offer six programs. Programs 1-4 can be purchased by a group (for example, Acme Company) or endorsed by the group and offered to group members for individual purchase (Joe Smit…

Hi - this is my first post, hope someone can help(!) ...Here's my problem; I have an table linked to & . I need to display a portal showing a 'see also' reference(of the *same* table). So, for example, an asset named will bring up records in the portal such as , , ect. Thanks in advanced!

We have a customer who insists we use # (Pound Sign/Number Symbol) in their company name. The # character is contained within so many records and tables which makes me very hesitant to change anything. The # symbol MUST be used when sending data back to the customer and to a third party which makes me even more hesitant to change anything. So is there anyway to create a valid relationship on fields that use the # symbol? For example match when fields in both tables contain the text: MyCustomerStore#0001

Rather than describe this problem in esoteric terms, I'll be specific. I'm helping a friend (it really is a friend) with a database for a roll-playing game (I'm guessing some of the people here know what those are). For the simple example, I have the following tables: - Character - Skills (list of all available skills) - Advantages (list of all available advantages) - CharacterSkill Index (which characters have which skills and at what level) - CharacterAdvantage Index (which characters have which advantages) The trouble is with (what I'm assuming should be handled by) another table, which is: - SkillAdvantageModifiers (lists skill/advantage combinat…

Can I view Table Occurrences as a List instead of a Graphic Chart?

I am using FileMaker 8, and have three databases, each for a type of women's health issue. The three databases need to be combined. They all have the following, which are unique: ~500 records ~250 defined fields ~50 value lists The above are organized into 8 tab panels each. Is there a way to (fairly painlessly) combine all this information into one large database? Thanks! Alisun

Hi everyone/anyone? I am trying to design and implement a [collection of] database. The content of these are based around trying to track 'widgets' in an exhibition system. I would like to have a db [1] of the individual units and then the 'currently available number of these units'... Some of these units are sent out to shows/projects [db2?]and when the show finishes they return to our warehouse [entered back into current stock] I need to know on a daily/monthly basis what remaining stock I have available. [db3??] any suggestions on how to efficiently organise/design this system? Thanx in advance. :qwery:

Well, I didn't get any response when I tried to explain my problem using just words, so maybe a hands-on realworld example that actually doesn't work is a better approach... See attached file. I'm trying to filter the contents of the left-hand portal on the shopping list layout using a relationship that uses a global key matching a calculated key. But I can't get it to work. I don't recall which of the 10,000 attempts I left intact in this file, but it doesn't work and I'm stumped. I need someone with working brains to show me what I'm too thick to figure out myself. ..Todd shopping_list.zip

fairly newbie question: I have a contracts table. I want to add a vendor field and a button that will open the vendor table, allow me to select a vendor and populate the information in the contracts table. I know i will need a new vendor layout with a button to select the record, but i cant figure out how to script this. thanks for any help!!

Hello all, This is my first posting. I'm in the middle of creating a new database for my work, and have had no prior experience with Filemaker Pro. I"m currently using FMP 8 on Mac OS 10.4. I would appreciate any help that you could give me. And please let me know if there are "keywords" for the types of problems I am mentioning, because that will help my future searches on this site. I work for a commercial film editing house, which means we take footage from Advertising Agencies, and edit them into TV commercials. I am in charge of creating an INVENTORY DATABASE for my company. Someone else created the majority of the database (which includes files for bill…

What does this mean? I placed a field in a related tables layout and this is what shows?

hi im building a databas with a contacts table, and a table for notes connected to the individual contact. the idea is that for every contact you can make notes, t.i. one contact can have many notes. what i want to do right now is to have a button on the contact side, that when pushed creates a new note, and automaticly fills in all the needed contactinfo (name, adress, etc.) on said note. i already have a note_id (to keep track of individual notes), and contact_id on both sides to connect the one-to-many relationship. how do i get the button to work? thanks

I can't figure this relationship procedure out... I have two occurences of the same table: AllParts and SomeParts. I want a portal (showing SomeParts records) to list only the parts that meet certain user-defined criteria the user has set using global fields. For example, the user selects color="green" from a pop-up, category="bolts" from a second pop-up, and vendor="acme" from a third pop-up. So I want the portal to show only the green bolts sold by Acme. Here's what I tried unsuccessfully: - added a global "criteria" field that contains a shorthand code for the user's selections. - added a calculated field that generates the same shorthand code …

I have been out of FM development for a few years and now that I'm back, I seem to have forgotten some very basic things. (I guess that happens when you switch to MySQL and PHP for awhile!) Should I use a global text field to allow a user to select the row from the lookup table? I only want to store the foriegn key in the master table.

I'm a novice filemaker developer and I'm creating my first database with the data and interface in separate files. I have all layouts, value lists, and scripts in one file and the tables defined in another file. Hopefully this will help make it easier to make changes to layouts and scripts away from my client. Now my question. Where should relationships be stored? In the interface file or the data file? Or do they have to be in both? Layouts depend on relationships as do tables with calc fields. Thanks for any tips.

Hi all. I'm having trouble (among many things) getting my head around this problem of relating many customers to many phones. The context is this: we're a drycleaner. Customers can have many phones (home, work, cell, etc.). A phone number can be used by several customers (husband, wife, children; employees working for the same company, etc.) If we need to call a customer, we'd like to display all their phone numbers. Also, when customers walk in the store, they give their ("main") phone number; at that point, I'd like to be able to enter the phone number and see ALL the customers that share that number. I've set up tables for Costumers and Ph…

Hi guys. I am working on a database project and I seem to be going around in circles, so I'm looking for some help/suggestions. Our organisation keeps data (basically contact information) on companies and individual people, and there needs to be a many-to-many relationship between those two. The companies and individuals can also be members of our organisation. We need to keep track of additional info for members (member fees etc). I know how to set up a many to many relationship between companies and individuals, with a table for 'Companies', table for 'Individuals', and then a 'Join' table. I just have no idea how to handle the members situation. Do I …

hello, i'd like to ask the pros out there if it is possible to filter records with (dynamic) value lists. i've got two tables ( A and B ) with the following fields: "contact no", "first name", "last name","school" and "city". (i don't want having to click on a search button to filter the records, thats why i'm looking for a relationship solution) I want to filter in table A records from table B being able to use all the fields (but not at once) as filtering criteria. While it is easy to set up a relationship that lets me filtering records as I want for ONE field (with a portal and a relationship), I havent found a solution that lets me filtering re…

Alright, I'm designing a solution that will auto-reconcile investment plans and find where discrepancies lie with a bunch of accounts. Currently we do this by hand- using filemaker will save us GOBS of time. And hence make my boss happy. : I've got it calculating differences no problem, but here's where I run into trouble: There are 3 types of identifiers for each account; one table will have all 3, the other will have only 1 or 2 out of the 3. IE: Stock ticker name Account ID type 1 Account ID type 2 when I put them into a relationship, filemaker only matches records that have the ticker AND ID1 AND ID2 all matching. Is there a way for…

3 tables main – invoice information billlineitems – line items feetype – typescost of line items issue – list of line items comes from list of fee types With the exception of discovery copying – all other amounts are variable Therefore, require it to automatically calculate if photocopying, otherwise be manually entered Have to change the calculation to a look-up so if the price changes, it doesn’t effect the previous bills retrospectively. The main issue is it doesn’t work well in a portal, and from the source table you need to click on refreshcashe results for it to update. For example: Add A4 Black and white - 10 pages. Works fine. B…

Hi I'm trying to display in a portal, a subset of a table's records, based on a calculation. pseudo code: if itemQty is =< minimumAllowed, then display that record in the portal. What is a simple way to do this?

I'm sure there's a logical explanation for my issue, but I can't figure it out. I have a file with two tables, Projects and Tasks. These two files are keyed on the ProjectsID. I have a portal on the Projects layout where I can create tasks. In the Tasks table, I have a Projects lookup field to pull the Project title into the Tasks table. If there is a record (even a blank record) in the Tasks table, then everything works fine, but if there are no records in the Tasks table, when I create the first task record from the portal on the Projects layout, it will not look up on the ProjectID and pull the Project title into the first record, and the first record only. Can someone…

I'm trying to create a receit database, where few values from my table would be coppied to a new table and I could print it out as a receit of values. I've spent hours trying to create relationships between tables and I can't get the 2nd table to display the values from the 1st table. 1st table has about 10 fields, of which 5 are numbers and one calculation field, I'd like to be able to view those same number fields in either a different table or a dif.database altogheter, so I could just print those out without having to print out the whole 1st table... Seems like a very basic operation, though I'm very new to filemaker pro8, I can't seem to get it done. : I've created…

Good morning! I have a database with 2 layouts. They are similar but differ in a few ways. One is a service call ticket with a portal showing billable activities and the other is a service call ticket showing non-billible activities. The relationship is one TICKET, many ACTIVITIES. The primary key is TICKET_NO in the TICKET table and the foreign key is TICKET_NO in the ACTIVITIES table. Each ACTIVITY record also has a field called TYPE where the valid entries are BILLABLE and NON-BILLABLE. I have built my relationship via TICKET_NO and TYPE. I have a global field in the TICKET table where I can either choose BILLABLE or NON-BILLABLE. The portal dynami…

I have two child tables that I am trying to summarize into one report and perform calculations upon. Both are many to one related to the parent. Other than having a copy of the key the parent is not involved in this report. I want to Summarize(group) the report by the date(month) from one table, and sub-summarize the report by selected listvalue from the other table. The table/record housing the date contains a number. The table/record containing the selected listvalue contains a percentage. In my report, I want to create lines containing calculations involving both number fields (for the moment, the calculated product of the number and percenta…

In table one there is a "full_name" calc field that combines the first and last name. In table two there is an "extras" field that has a value list to choose from the "full_name"s of table one -- once chosen sucessfully triggers several lookups in table two, as it should. There is "relationship1" set up in table one to table two, full_name::extras. In setting up the script step "go to related record" in table one, the "relationship1" is seen, the option for using the external table is seleced and the layout for table two, form, is selected as well. When trying to execute the script, there is a message that the operstion could not be completed because t…

Hi all - I've been handed a database that someone else started and told to add some features. Above is a screen cap of part of the relationships graph. On a layout based on the "Cert" table, a portal to the "Part 3" table occurrence displays the "Number" and "Desc" fields from the "Parts" table. Clicking on a row in that portal will automagically populate the "Part" field in the "Cert" table/layout with the value from the "ID" field of that record from the portal. The "Part" field in the "Cert" table is not defined as a lookup, nor is it displayed on the layout as a related/merge field, so I know it must be populated from the relationship, but I don't underst…

I'm not sure if this forum can help with this... but this is the last place i can go short of paying someone somewhere (And that too I don't know where to go for). It is a very specific Filemaker Pro question. I am building a filemaker pro database to serve as an invoice system for a part time business I want to run as a hobby. I have made one database, stating my products, description, price, and tax. I have made another one that is a client database with their names, addresses, contact details, etc in it. I wish to make a third database, that is an invoice database. I wish to create a record, and input all data about a said product from my first dat…

I am trying to create a summary page that includes all events (in portals)accross multiple tables (vacations, events, to dos) that are equal to todays date. I was able to get the basics to work but there is an issue with events as follows; An event can have a range (start date to end date) and then frequency (monthly, bi-monthly, weekly, daily) and then can have the day of the week it occurs on. So the trick I am missing is how to let FMP know that the event happens to occur equal to todays date. Any help would be appreciated.

I recently converted from FMP6 to FMP8. FMP6 didn't have tables...at least not the way I understand it. If I wanted a related file, I just created a separate one that I related to the first, with each containing a common field, and then I created the necessary relationship. Now I'm trying to understand tables within a single file. I've ordered a book on FMP8, but it hasn't arrived yet. I understand how to define tables...I just don't understand how to relate them to the main file and populate the fields with data using a drop-down list. I know...this probably sounds too easy for words, but I'm missing something somewhere in my thought process. Here's my…

Hi there, Are there any general guidelines as to how many relationships one file or table should have with another? One of our DBs has developed quite a few relationships due to the creation of various specialised reports and I'm concerned that there may be a limit. Does it depend on the server processing power only? We have the latest Macs so our hardware is good. Cheers, Steven

I'm having trouble wraping my head around some relationships and was hoping for some help. This post deals with data that might be related to both an individual AND a group of individuals. Thanks in advance. Example: A tutoring organization Mary is a student. She has two parent/guardians - Frank and Susan. Mary has a brother - Sam - Sam is not being tutored. However, we would like to keep track of siblings because they might be a source of more business. The Family (group) has three address - home1 (the billing address), home2 (the home in this area), and location of tutoring (library) The Family (group) has two phones - land line 1 (home) and land line …

I am planning a database of Organisations and Staff. The idea is to show Organisations with people employed by these organisations. Organisations would be categorised into relevant categories (such as Affiliates, Service Providers or Training). Organisations would always have the same address with the main phone number, fax number and main email which would be common to all staff. However, staff members could have several phone numbers or emails (work, personal, mobile etc). In addtition to this staff members could have different roles such as Trustee, Director or Policy Officer. My plan is to have three tables: Category, Organisations and People. Category is one-…

I'm sure there must be a thread where this is already in discussion, so please point me on my way as my searches have proved futile. I am apparently keyword challenged. Faced finally with a meaty project to build from scratch in FM8, and with the users and server accessible only via a SLOW T1 line, I salivate for the separate user interface, so the data can stay put. I would love to hear about real world experiences, but I wouldn't snub my nose at theory and conjecture.

I'm trying to solve a report/relationship problem in an application and I've resorted to putting together a simplified test database. I've closely followed a textbook example in setting up table relationships to get a summary of invoices from a specified start date. The portal version works fine, but I can't come up with the right combination of layout definition and table references to get the same result in a printed summary. I will appreciate any recommendation on a proper direction to take. Thanks in advance. Invoices.fp7.ZIP

Hello Everyone, I was pondering the idea of adding a Check Writing feature to my solution. My question is… Does anyone have some resources I can check out and read up on? If anyone can point me in a good direction it would be greatly appreciated. Thank you!

My question is similar to one posed by Ken M earlier today (newbie problem - relations and lists), but I can’t figure out how to solve my problem from the response posted by Martha I have a file that is used to construct a life-history database for clients. The file includes 4 tables 1) Clients, 2) Sources, 3) Events, 4) Names. Records in the Events table are tagged with themes from a value list. The field Theme in the Events table is defined as a repeating text field formatted as a pop up menu which displays the values from a field named Theme List, which is the Clients table. (Each client has his own list of themes). Each record in the Events table is associa…

I'm using FM 7 and I want to set up a relationship that picks out members of a class OR of just a subclass, depending on what the user requests. For simplicity, I'll set the problem up in terms of color. Let's say I have a table with six records in which one of the data fields is color: Data table - color subclass record 1 - true red record 2 - true blue record 3 - teal record 4 - true blue record 5 - mint green record 6 - tawny At present, I have a simple 1-1 relationship linking this table to the main table. When the user chooses a color from a pop-up menu, the relevant records are displayed in a portal. But teal and true blue are both subclass…

Although I've managed to build several intermediate-level portal usages into my applications, I continue to run into traps with combinations of TO's, global fields and "show records from:" on layouts. In hopes of being able to simplify a couple of my user interfaces, I've prepared a test file for two very basic situations. TEST ENTRY is supposed to be a simple portal for reviewing the contents of the Test Table and for entering new Tests, regardless of which student the entry is for. It needs to show all of the Tests. Is this a valid use for a portal? STUDENT SCORES is supposed to show an individual Test with Score and be able to step through each Test f…

I've been challenged to create a database for a local organization to use at an annual holiday basket fundraiser. Here's the rub: The system would resemble any inventory/order processing system, except that the customer list would also be the product list. Plus, there's a "reciprocity" option, which I'll explain in a minute. Please help me set this up! Here's how the fundraiser works. All past and present members of the organization receive in the mail a list of names, which includes all those past and present members. The recipients check off the names of people to whom they want to send baskets, then return their forms (with a check, of course!). The size of the ba…

I'm having a look at FMP8 trial to see if it will d what I need and am testing it's relational capabilities on a fairly simple contact management system. There are 4 tables with relationships between each: * Organisation (org) * Office (org, office) * Person (org, office, name) * Contact (org, office, name, date) On the 'person' layout I want a list box to select their organisation and another for their office so I've defined a list based on Organisation and that works fine. I then defined another list based on Office but cannot get that to work properly... If the list is unconstrained then I get ALL the offices in the database, not just for …

I'm trying to create the following scenario... I have a table of inventory parts that lists parts numbers, description, qty on hand, vendor, etc. I want to create a "shopping list" which is a lookup (copy, not a direct relationship), of parts that are running low. I'd like it to look like two portals side by side, the left one listing the parts table, the right hand list showing those parts I've selected to add to my shopping list. There'd be a button between the two portals, to [Add] a part to the list. It would work exactly the way we add a script step to our scripts. I can't wrap my head around how this can be accomplished though. And I'm sure…

If anyone can help it would be greatly appreciated. I have 2 tables only joined by a constant relation using a calculated value of "1" in each table. In table A I have 4 fields that are calculations (dollar amount totals) that I need to pass to table B automatically. I have no problems accomplishing this by setting the field in table B to a calculation and setting it so that it automatically places the data from table A. My problem comes in when the user performs a search in table A and work with only a current found set. When this occur the "dollar amount totals" calculation fields that I have in table A update correctly however the calculated results in table B …

Hi everyone, I am new on this forum, so I hope this was the correct plac for my question. First of all, I am from Argentina, so excuse my english. My problem is this: I have a designed database for contact information for the attendees of a conference, so, what I need is to send this info for everyone in the database, but for the people with 2 or 3 participations, sent just 1 e-mail with all the info. I have made my layout with a self related table, so when the Last Name is found, is displayed on a portal, with all the info of that particular participation with the date, hour, subject and what kind of activity will be hold. I used to do (and I am …

Hi.. Does anybody have a workaround for using the min() function on a found set of records only?? Seems like min, max and average apply to all records in the file, not just found records. Seems like a pretty basic thing! (FM6) thanks

Hello All. I have a question that might be simple for some of you, but it's been stumping me for weeks. I have a database of financial aid awards. Each record represents one award. Records contain student id numbers, and award types (federal loan, scholarship, etc.). There are about 40,000 records, and, within those, about 4,000 students and 14 types of awards. Thanks to some really great help from people on this forum, I've gotten as far as I have, but I need advice on how to get to the next step. I would like to be able to create a field for each type of award, and then total all the awards of that type for each student. My ultimate goal is to…

I am new to FM and a novice with relational databases and can't figure out how to do this. I have a DB I need to translate and allow users to switch the language (user interface) on the fly. My thinking was to do the following: 1- build a "Dictionnary" table with: LabelID, Labelname, French, English, Spanish 2- On existing DB, create "text_items" (using labelnames) using for displaying titles, field labels, etc... 3- Have a global store the language in use 4- When the user clicks a button (or drop down), change the all the user interface "text_items" by using the "Dictionnary table" and the global variable Here are my questions: A- Is that the best w…

I have two databases with ovarian cancer patient data: Database #1 - General patient info (name, date of birth, medical history, family history, etc.) Database #2 - Clinical Trials (patient name and the clinical trial(s) in which they have participated. Is there a way that I could make a relationship to put all the clinical trials that a patient participated in, into Database #1 ? There are approx 400 patients, so I'm not certain whether this is possible....? Thanks, Alisun

Hello Im new to filemaker and whist i have the main d/base in operation im struggling to get it to read data in a related table. I have the table set up with its foreign key related to the primary key in the main table but it refuses to read the data (but the main table reads data from its own ok) help please

I realize that a one to one relationship is almost non existent, but I believe one is applicable in my situation, but I'm not sure how to set it up properly. I have defined 2 tables. Businesses: (The complete list of business names to solicit potential customers) Customers: (A customers list derived from the business name list who become customers .) One business = one customer. The only way I can get the relationship graph to recognize the relationship as a one to one relationship is to create a business ID primary key serial number for the business table, and a business ID primary key serial number for the customers table. Both serial numbers are d…

I have a testimonial table which has a CompID, CustID, custName, TestID and testimonial fields. I have a portal which allows for the creation of 3 testimonials per company. I then have a form which needs to display each testimonial on a seperate line with the customer name underneath. I tried using a repeating field but couldn't get it to work. And I can't seem to get all the testimonials to display using the latter way. Can anyone help?

Alright so I'm not really sure where to put this on this forum so maybe the moderator can move it for me. ....I'm trying to make a database where people can register a username and using that username insert information such as their name and how many points they have along with the group they are in. I want the database to be able to add up all the points for that group and get a total. I think I can manage to do that part myself but I have no clue on how I would make it so they will be able to register their name and then later edit their information on the table using their name and password. (I don't want people to be able to edit each others information, only th…

I have a relationship that allows me to populate various fields based on customer name and buyer. The fields that populate are shipping address and ship via. Sometimes I need to override the auto entry. When I do this, I change the value in the linked DB, parent I think. How can I change this without changing the source DB?

Hello all, Thought someone else would have asked this but could not find it. If there is a post, please let me know. Trying to put togeter a quote to job database. Here is how I have set it up Quote Quote_Item Job Job_Item Both Quote and Job can have only one record, but they can have many items. I would like to create a quote, then if it becomes an order "import/copy" items into a Job. On Quote layout, have a portal that allows creation of record to Quote Item. This allows me to enter as many items into the quote as I want. Then if it becomes a job, I have a link to list the quoted items and then I re-enter the job items the same way. It …

Greetings forum goers. I'm trying to track some data and as usual I can't wrap my brain around it fully. I know what I want to do, which will be explained, I'm just not close to certain on how to bend FMP to make it a reality.. (isn't that always the problem LOL). So here goes.. I have multiple modules each with a varying number of ports. Each user is assigned to a module and one of the module's ports. I want to view data on a module level.. ie.. Show me all users on Module 1 and what port they are on or on a user level.. User A is on Port 12 on Module 3. Ideally when adding a user I should only be able to select a port that actually exists on the selected modul…

I have three databases, each of which could contain a record or records that may list the same Version Code in one to seven fields. My users needed a tool so that they could locate a version code by searching all seven fields of all records in all three databases. I built relationships between these three databases so that in a fourth database the user only needs to key in the version code they wish to locate. When this version code is keyed in, the portals I defined get populated with the record identifier of the first record in which the version Code is located. If it is located in 18 records, that same record identfier appears 18 times. How can I get all …

Hi all I've been staring at my screen and scribbled notes/ideas in a notepad for a few days and am drawing a blank. I am creating a database and just don't know how best to do it in terms of structure. Was wondering how some of you experts would go about it to get me going. Firstly important bit - using FileMaker Unlimited 6.0, meaning any relationships will be between databases (not tables as in later versions) and secondly I am planning on hosting this to the web at a later date (if that makes any difference to the structure) OK the database itself I am creating a contact database. I see it as 3 databases Company Company contact names In…

Here is my Question, I will like my portal to display records according to the value in the switch key field in table I Thanks. States.zip

So I managed to build the Invoice, client, purchases and items tables and get them to work fine but I'm stumped with the credit notes. The Credit note table is related to the Invoices by Invoice number and thence to the Client table by the same relationship. If I relate to the line items table by invoice number I alter the invoice and stock. Do I have to build a credit line items table to lookup the values from items without affecting the stock, ( as the credits are for fresh produce any returned items are fed to the pigs and do not go back into stock). Thanks for any help

Hello. I've kind of been thrown into the deep end in terms of database software. Having no idea even about what a relational database is, I've been asked by a friend to come up with a job/personnel tracking database for their small business. I downloaded the trial version of FileMaker Pro 8, so I now have approximately 26 days to try come up with something that will convince them it is possible. I began by fiddling with the "Task Management" template that came with FM 8, as it seemed closest to what I wanted to achieve. But now I'm stuck, and I think it's because I don't really understand the idea of relationships. Basically, what we want is this: someone w…

Recently, it came to my attention that one of my solutions had some errors in the referential integrity rules for the data. Specifically, I'd forgotten to ensure that the Delete records option was checked for several of the relationships in my app. This got me to thinking about the Relationship graph, its difference from an Entity-Relationship graph, and how that might play out with regard to cascading deletes. The Relationship graph is something of a hybrid, combining an ER diagram and query diagrams. As a consequence, a pair of related tables can have multiple table occurrences with multiple relationships on the graph. So my question is this: does anyon…

If I have Table1 that can have multiple items in table2, is there a way to set up a relationship that will show any items in table2 that aren't associated to a parent record in table1? So basically show a portal that contains all the records where the key field to table1 in table2 is empty.

If some pro can help me... I have two related tables A and B. The A table have cases with ID numbers and the court name. The B table have some especific and repetitive diligences and their dates. My mind is very limited ( maybe for that I´m a lawyer, and my english it is as it is), and I can´t find the way for work with tables like a no relational database. I only need a "Excel look" spredsheet, with all: the cases, their ID number and court name, and the diligencies made with their dates, all in one presentation, for my assistants court work. Some help maybe to mean the end point for months of work.

I have a portal that displays data from an invoice layout. I am able to sort the portal by either date range, status (ie paid, not paid etc) and by employee. Here are the fields relating the status variables: (Portal) (Invoice) cbilling_status=status cbilling_status (calc) Case(IsEmpty(x_status); ValueListItems(Get(FileName); "Billing Status"); x_status) xstatus (text): global field, located in the portal header, allows me to see all invoices if it is left blank status (text, indexed): Pulls data from a drop-down list named "Billing Status" I would like the values for "Billed", "Paid" etc to have individual text colors (blue for "Paid",…

I'm trying to learn the rudiments of data entry when creating a two-way join table, using one of the parents and a portal for data entry, as opposed to creating the association in the join table layout itself (either in a join layout directly by the user, or scripted). Following is what I've done so far, and it seems to work, but I wanted to check if I'm missing anything. I've kept this as simple as possible. I'm following normal conventions for the primary ("kp") and foreign ("kf") field options. A 'song' must have 1 or more composers. A 'composer' can have composing credits on 0, 1, or more songs. Tables: Composer: kp_Composer_ID; ComposerName So…

Hi, i am trying to build a database that contains a table for Customers. For each customer in the table i want to store possibly hundreds of records. I did set up another table called "items" and tried to relate this to the customer using the common "customerID" field, but i dont know how to make it so that when i enter a new customer in to the customer table, i get a clean slate when i go to enter the data for the new customer into the items table. I don't know how clear that is. I thought i could somehow build in a sub-table for each customer in the customer table but i dont know enough of fmp to know how to do it. Maybe someone out there knows of some…

I'm using the inventory template. The problem is that I have a many-to-many relationship that I don't know how to handle in Filemaker (I'm new to FM, but have database design experience). Each product in my Inventory table can be assigned to more than one category. A category can be assigned to many products. So, I built a bridge table called Inventory_Category to hold a concatenated key of the InventoryID from the Inventory table and the CategoryID from my Category table. Now when I'm in form mode and I'm entering a product, I need to be able to click a button (or do something) so that I can add multiple categories to it. How would I build this and have it insert th…

Hope this is the right place for this one. I have 2 tables; Inventory and Receivable. On receivable Layout I have a portal to allow entry of about 5 fields. Then the dimensions of the item need to be entered. We are a job shop, and items received need different inputs for the dimensions. A round bar only needs 2 dimensions where a plate needs 3. To make the process of entry easier for my users, have a field in portal to select Invetory Type, push a button that opens a window to enter dimensions only then just close the window. All possible dimensions have fields on the Master Inventory record. When I try to enter data in the fields on the window, I get err…

Hola! Great site here,, I've done some searches but haven't found what I needed yet, so here it goes: Creating a database to keep track of 'People' and their relationship amongst each other. Example: Johnny Smith, son of John Smith and Jane Smith, nephew of Bob Smith, friend of Hillary Dvorak, brother of Jill Smith, etc. This part I have done already with One table for People and another Table for Relationships. I go to Relationships table, enter the different IDs for two people and specify relationship. The difficulty I have figuring out is how can I, then, simply go to John Smith and have it show Johnny Smith (his son)? I don't care to show h…

I've been working with filemaker since a very early version so creating a flat file type database for me is very easy. Even using lookup fields to populate information was easy. Using a field to see data from another table is somewhat simple. The problem I start to panic at is when I need about ten tables for various things and then realize that I have no idea what I'm doing and how I"m going to tie this stuff together. I mean, say I have a book table, an ISBN table, and a person table. In very rare cases an author can be an editor and sometimes an artist as well. Do I need three "title" tables or maybe one person table and a title table? Check boxes for each ti…

I am working on a database to store data from scientific samples and I have a question regarding the best relational design. For example - I have a 'main' sample table that stores the lab code for each sample and subsidiary tables that contain other data, for example, patient data (name, age and so on). Should I relate everything off the lab code primary key? So that this 'main' table will virtually be a single field containing the lab code. The other tables will therefore contain primary keys that are not use for relationships. Or should I create more fields in the main table that relate to the primary keys in the other tables? So that the sample table wou…

Is there a way to save copies of your letters everytime you create one? I want to keep track and record all the letters/invoices I write to people in my database, but everytime I need to type a new invoice, I have to save over the old one. How can I save all my invoices so I can retrieve them at later dates if needed?

I have a somewhat complex related table problem; At the moment, I have a table Sequences with data of the sort [color:green]Patient - text [color:green]Time - number [color:green]Sample - text [color:green]SeqLength - number In [color:black]Sequences, there are often several entries that share the same [color:green]Patient, [color:green]Sample and [color:green]Time values. I now need a new related table Visits that has a single record for every unique [color:green]Patient/[color:green]Time /[color:green]Sample combination. In the new related table, I would like to have a field [color:red]AveSeqLength that is the mean of all of the [color:green]SeqLen…

Hi everyone, I'd like the supervisor field in our main layout/table ("2005 Guide Distribution 2" table) to show the supervisor of an employee based upon the employee's section which is listed in a seperate table ("Employment History" table). Supervisor information (section and supervisor name) is located in a seperate table as well ("Supervisor" table). I've attached an image showing current relationships and tables. Any code, calculations, relationship advice, etc. would be very helpful. This is the last part of the database and I just can't get it figured out!

Hi I'm pretty sure this is easy, but I've been staring at it so long I've completely lost the plot. I'm putting together a staff database and I'm stuck on the payroll entry section. I need to be able to enter a week ending date and then be presented with a list of current employees for which I can just go down the list and enter amounts into a field. I hope that makes sense. If anyone can help it would be greatly appreciated. Many thanks!