Relationships

Hi there, I have DB with 400,000 records in it. To keep it simple lets say there are 4 fields in it (a)=unique value (=every record has one(there are about 150 different values) ©=every record has one (there are about 5,000 different values) (d)=number (can be null) I want to be able to select a (b)and then show all of the (c')'s related to the selected (: but only 1 of each of the ©'s, no duplicates. Then i would like to show the total of (d) for each of the unique ©'s. Then it gets more complicated but for now that would be a great start. I have done this in the past with exports, scripts and lookups but i think there has to be a better way. I…

Without using a find routine, how would the syntax of my calculation look for the following solution? A member has a bar-coded 5 digit unique membership number which is scanned in which becomes a transaction, date and time stamped. Call this 5 digit field Member_ID. We must keep track of this Member_ID during the day. He can only use the number once a day. If the card is scanned again later the same day, we must be alerted at the time of the rescan via a message to the operator. "Member has already used this card today. Allow? The key is to relate his usage to todays date. If he uses it tomorrow, its fine as long as he only uses it one time. …

Good Day, I'm having some difficulties. First off, I want to have a field on my primary layout which allows me to either select a value from the corresponding field on another table or, if the desired value is not available, be able to add it by typing it in the field on the primary table. This seems like a simple process, but I'm not getting the results I want. Either the field fills in with the ID value for the record from the associated table, or, if two records of the primary layout have the same value for this field, I get redundant records on the secondary table. Also, I'd like to have a field whose available values are dependent on the value of a pr…

Databases: Account DB Site DB Contact DB Relationships: Account DB Site DB A.Accountid = S.Accountid Contact DB A.Accountid = C.Accountid Site DB Account DB S.Account_ID = A.Account_ID Site DB S.Account_ID = S.Account_ID Contact DB S.Site_ID = C.Site_ID Contact DB Account DB C.Account_ID = A.Account_ID Site DB C.Site_ID = S.Site_ID Contact DB C.Site_ID = C.Site_ID I can add a new site to an account and a new contact to a site, but I have no relationship when it comes to creating a new contact for a new site. Is there any way to pass variables from on DB to an unrealted one, or any other way to create the r…

Hello and thanks in advance for your help. I have 2 tables. 1st is an inventory list. 2nd is a sales table. We sell products the are reduced in price every 15 days until sold. When sold I need to enter the current price into the second table. I have this calculation for 'SellingPrice=' but I get an error that say's 'too few separators'. Any ideas? if (SellingDate < DateIn ) if (SellingDate > ThirtyDayPrice ) if (SellingDate > FortyFiveDayPrice ) if (SellingDate > SixtyDayPrice )

I'm having trouble determining how to design my fmp9 db. I had a solution that worked but was not user-friendly, and my attempt to make it user-friendly has resulted in a mess. Each day I need to specify --which Projects I worked on --which Tasks I performed for each project that day --and what set of Resources I used for each of those tasks. I have separate tables for possible tasks and resources, and then I have these additional 3 tables: Projects, TaskEvents (where the Date gets involved), and Resourceevents. Its very user-unfriendly to make a new record on a separate screen for each project/task/resource combo that occurs in one day, and so…

I have a table with thousands of records, which have identifiers of various length. i want to create another table occurance that only shows a subset of the records, based on the length of the identifier. I created a Length(ID) field and another stored caculation field with the desired length (e.g. 17). Both are set as number results. i tried creating a relationship between the two tables with these fields, but it is pulling all of the records in, not just the ones with the IDs that are 17 characters. Am i missing something? thanks!

Hello, I have a many-to-many relationship with two tables ('AAA' and 'BBB') linked by a join table. Both sets of data can be assigned a contact in a fourth table. To do this I had to set up a 'ContactsAAA' and a 'ContactsBBB' table occurrence. So far this works fine. Now I would like to be able to have a contacts layout with two portals visible, showing related AAA and BBB records. Obviously I need to find a workaround to get another table occurrence, on which my contacts layout can be based. If I try to link 'ContactsAAA' and 'ContactsBBB' in the relationships graph, FM tells me 'there cannot be more than one relational path between any two tables in …

I attach a file. (There is only one table with 3 fields and 2 TO's) I have created a TO (called "TasksToInclude" and a layout that should only have show those tasks with the text "include" in the "TaskStatusText" field. The TO TasksToInclude is a based on a calc field in Tasks called cTaskIDs. The layout is showing records from Tasks but displaying related fields. I was about to write a script that would "go to the TasksTOInclude layout and export all records (ie those records that where the TaskStatusText field = "include") Part of the export script would have been to then set TaskStatusText field to "exported" (or maybe another date field wit…

I need to find two specific fields in LineItems from within another table. I tried Lookups, but I apparently don't know how to get a specific field, and I tried a multi-criteria relationship, but it seemed pretty cumbersome. Here's the tedious explanation: I have the standard Invoice, Products, Line items, Addresses setup with an additional Checks file. Checks, among many other fields, displays the LineItems sold on a particular concert night, where Invoices displays the LineItems sold on our website. In the Checks layout, I have a portal with all the items sold at that event. However, we discount sales according to volume, so 3 CDs sold separately will be $…

Hello all! I've been on the forums lurking around looking at peoples tips and feedback. Just wanted to first say "hi" and nice to meet everyone. I got into filemaker a bit over a year or so ago in hopes that using it would solve some tedious issues for myself at work and at home. Making small databases to keep track of stuff and to eliminate some very tedious calculating. The few projects which I have done have really become more of an "elaborate" calculator which I can plug in a few values to and get my desired result and organize them for a quick reference if needed later. Recently I've taken on a project for work which has required a bit more thought and …

A few years ago, I built an inventory tracking system for a company that rents equipment for film productions. I used calculations to determine stock levels, and, now the db is slowing down quite a bit. I'm doing a rebuild and want to move from calculated stock to scripted stock. I'm ingesting Todd Geist's Transaction demo from DevCon, which is good, but is limited for what I want. I'm trying to visualize what techniques to use for a Rental inventory system in which the stock levels change at pre ordained days in the future. I don't know if this is possible to do via script, but any ideas are appreciated.

I have three tables, call them Trips, TripRoster and Clients. Relationships are set up properly with "allow creation of records via this relationship" permissions set. And in fact it all works fine in terms of displaying existing data. The problem is with creating new, related records. In a layout for the top-level Trips table is a portal. The portal row contains the key field that relates Trips and TripRoster "tripRoster_ID". also are fields for the Clients table (first name, last name etc.) When I click on the empty portal row to create new related records, it seems that no records are created until I enter data in a TripRoster field. So the auto-generat…

I have a table with invoices, and a table with the current invoice number. 3 users are connected to the server wich is hosting these tables. I've been using the global variable function to store the most recent invoice number. I also have used the "Set field" function, something like this: Set field: invoice number; number_table:invoice number and then, I must update the invoice number so I use set field again and enter invoice_number+1 this works really great with one user, but not with 2, because the global variable is not global enough. It's only updating on the local computer, and not on every connected client. How can I solve this issue?

I'm new to FMP and just in the planning stages. I have numerous main tables inc Contact, Property & Journal and I want to be able to create a related record in a Tasks/Schedule Table from each of these. Should I just simply have a separate foreign key field from each of Contacts, Propery & Journal? And therefore only one these fields completed in each record.

Hi, I have a table of song titles ("Song LIst"). Some of these songs are variations of each other. e.g. "Happy", "Happy w/o Tuba", "Happy strings only". When I look at a record for one of the "Happy songs", I want to be able to see the list of the other versions of the songs. I want to be able to set which songs are related or not manually and not set an auto-relationship. How do I go about doing this? Thx:)

I'm new to FMP, and I'm not sure how to accomplish something: I have a Contacts table. It's related to an Orders Table via a contact ID. The Orders table is then related to a Line Items table via the Order ID. In the Line Items Table is a Webinar ID. This is related to a Webinars table. So: Contacts->Orders->Line Items<-Webinars In the Orders table is a field called "status". The Order status can be "Completed", "Pending", or "Failed". What I'd like to do is when viewing the record of a Webinar, show a list of contacts who have ordered that Webinar and completed that Order, as well as show a separate list of contacts who have ordered, but th…

FMP tries to indicate the cardinality of a relationship using a straight line or "crows foot" up to the TO. But there is another that shows up occasionally that I cannot find any references to! I wondered if there was anyone out there that could point me to one or more places to find out what it is meant to represent. I'll attach a jpeg to illustrate.

I have three tables of data. 1. Calendar (SeedCode) 2. Billing data 3. Users Table I need to have the calendar show billing data summarized by user. For a given day, the calendar should look like the following: [Name] [Hours] User1 2.0 User2 3.2 User3 4.0 Note: Each user can have multiple billings in one day so if I didn't show summaries it might look like this: [Name] [Hours] User1 1.0 User1 1.0 User2 0.2 User2 2.0 User2 1.0 User3 4.0 I can't figure out the relationship to set it up. I can get all the individual billings for a given date (as shown in the second example) but I can't get t…

I had originally set up in Filemaker 7 a payroll DB for my business that was working fine. My business was just starting out so I only needed to tables Employees and Payroll. My business has now grown & I need a more detailed payroll. It seemed fairly straight forward but when I went to enter more records I couldn't get it to work properly. My business now has 3 different profit centres, some employees work only in 1 profit centre, some work in all 3 profit centres. I want the db to track the hours worked in each profit centre so I can set up reports to expense the actual time worked to the appropriate business. In the new DB I set up 4 Tables, Employees, Payroll …

I am trying to setup a solution to track patient's appointments and their payments for those appointments. Because appointments might need multiple payments and because paments need to sometimes be split across multiple appointments. I think I have a many to many design and need a join table. Attached is what I've set up so far, but it is not working as I would like. I would like to enter in the payemnt info and then choose which appointment to apply that amount too. However, that portal where I'm entering in payment is creating unwanted records. Patients.zip

I am a wedding Photographer setting up a FileMaker 9 database to keep track of contact information for my clients. The Wedding is the event with several contacts associated with the wedding. I have the Bride, Groom, Bride's Parents, and Groom's parents. I need contact info for all of them - address, email, phone. In the bride's info I also need records of payments that she has made and some notes, and the status of her album order. Should I set up one giant record with fields for everyone, or should I make some sort of relational structure that has a master Event with sub records. I will want to do searches for contacts that have not ordered anything.…

Hey, I have a question that I can't seem to address. When if comes to genetics, one can imagine calling a group of genes a particular name that I will refer to as a Strain. The group of genes are broken down into Alleles, that have a name and are unique. A new user comes to a system and enters a new strain and needs to build this strain by selecting the alleles that correspond to that strain. Geneticist like to order these alleles by importance. Because we generate new alleles everyday, one could imagine having a new allele entered into the system that is more important than the previous. Two tables: Strains: One to many Alleles …

I'm trying to decide if I need one or two tables for this issue. I'm building a Billing/Inventory Tracking solution. A user creates Orders which have children named Bill Items. Those Orders also have children Contracts, which have children Transactions. Bill Items look up Bill Codes and auto enter description, price, etc. Transactions look up Inventory Items and auto enter description and current stock. I'm trying to decide if Bill Codes and Inventory Items should be one table or two. For about 90% of the records, Bill Codes and Inventory Items are the same. But for about 10% of the records, Bill Codes are assemblies of Inventory Items (I have a …

I do have a relation between 2 TO (Tables) that way: STUDENT (ID, Name, GroupID_fk, GroupName (Calculation: Unstored: GROUP::Name), ...) GROUP (ID, Name, ...) STUDENT::GroupID_fk ----[=]---- GROUP::ID When I sort using the "Sort Records..." menu command using "STUDENT::GroupName" it doesn't sort and produce the same output as "Unsort" but when I do use "GROUP::Name" it does sort correctly. Till it's a calculation field using a field from another table, I can't force it to be stored rather than "unstored and calculated when needed" Is there something I missed?

please help me make this a little easier. I'm trying to find the first record with values for each person. its a One to Many relationship but not every first related record has a value. The value might not show up until 3 or 4 records of 10 or 15. If I use the GTRR record trick that I've recently started using I get a report with a lot of "first" records being empty. Any suggestions are greatly appreciated.

Hey all, I have a zipcode database with city and state. It is a basic one. I also have another database, Contacts. I want Contacts to draw from the zipcode database the city and state when the zipcode is inserted. I looked around, but do not seem to find this. Thanks,

I'm sure this is not difficult, but . . . I'm setting up a contact database with an email script. I've added fields for user to enter/paste for Email Subject Line and Text Body, etc. Included is a section so they can choose which categories will receive email. However, if I set up a special layout in contacts db I'm entering this into a single record, though it's not related to just that record. If I set up a separate table with only one record to house the email layout, I'm not sure how to link to contacts db to send email to all found records (by way of script). Sorry if this does not make sense.

Please see attached sample: I need to find away to check or make a relationship using two different tables with two different fields. I need to compare the Broker state with the table "broker_license_page" then I need to compare the broker company with the table "broker license_chart". If the broker state is Maryland but the broker company is not listed on that states page then no value given, but if the broker company is listed on the page it should return the value need. Thank you, Jospeh and return the value effective date and expires date. Policies.zip

I have a database with two tables, Registrants and Activities The tables are related by an ID in both. In forms I am using a Pop-up menu to display the text description from Activities. I need to send a confirmation email which should include the text description stored in Activities. Since Pop-up menus don't email well, how to I display the text description that matches the ID in Registrants?

Dear FM World, I have been attempting to learn FMP8 for some time, now. I thought I had several aspects under control after experimenting on a table/file/database without embedded photos. I then opened the larger file and added some bells and whistles. I then attempted to create a relationship. I was working with two files I had "saved as;" both were working fine. In the relationship graph, neither file name was present, so I deleted what was there and found the other (newly) named files and used them as the tables (btw, they retained "old" titles???). When I returned to the larger file, all of the fields had the message "table missing." The three tables are a…

I have a table of dates (one record for each date from Jan to Dec) and a table of bookings, which will be various dates and may have 2 or 3 booking on the same day. I can get the dates to display in order on a new 'list view' layout and as I scroll down, bookings show for their related date, as that's what they are join on. However, where there is more than one booking for a particular date, other records will not show. I did do this in Access and achieved it with a query. I had the diary dates repeat showing all the bookings. i.e. 01/02/07 02/02/07 A booking 02/02/07 Another booking 03/02/07 03/02/07 Yet another booking 04/02/07 I want it dis…

I have created simple relationships before but I am stumped on this one. For example, I have a database of contacts (around 1000 records), and a database of job descriptions (around 100 records). I would like to be able to have the contacts have a portal to where a pop up list would show Job Descriptions and you can select one and/or multiple job descriptions. When this happens, I would like a portal on the Job Descriptions automatically show all the different contacts that have that job description. I tried creating a direct relationship between the two using an Individual ID and a Job Description ID, however that did not allow for multiple entries to take pl…

Hey all, I could use some help on this one. Have two tables related by a simple serial key. When table 1 relates to table 2 I get multiple matches for each key. I can see these matches in a portal just fine, and I can even sort the portal. My next task was to parse each one of these matches to it's own field in table one, using the calculation: GetNthRecord(); this works great except I want the parsed fields in table 1 (say Value1, Value2, Value3) to be in a sorted order. Sorting the portal doesn't help me, sorting the original table 2 doesn't help either. FM seems to use some internal key that matches the creation order of the records of table 2, so when using GetN…

I created my first database for a festival in michigan to use to track money (how many 1's, 5's, etc..) and by area, booth and product which worked out great for the festival 2007. Now they want to use it for festival in florida. I'm curious if anyone knows how to add some admin specific data. My idea is on a daily basis the admin will set some settings that will be applied to all transactions: (location, temperature, weather, head count) which these four items would be included in the report that is run at the end of the day and attached to each transaction. Could I get some help/direction on how to make sense of the mess I created? I have a month to come up with …

Hi all I have made a relation ship where a match on 'street' AND 'postal code' relates to coordinate on a citymap. Since some streets covers more coordinates I would like to know if its possible to make a relationship where I can also add a third (relaxed) match, 'house number' to relate to the coordinate. (Ex. Any number from 1 to 100 relates to coordinate A1 and any number from 101 to 200 relates to A2) And what if the house number is 13A? Can FM be set up to only look for matches on the number and ignore any letter? Regards Lasse

Running Filemaker 8 Advanced In the past six months I have had relationships "break" i.e., layout shows related table fields as "table missing". However, when I check relationships the "link" is still in place. If I reclick the "link" between tables, the link reestablishes but will "suddenly" disappear at some point during data entry (during same session...no close/reopen). This happens randomly in multiple files that heretofore had operated over a period of years without this problem occuring. Note that this happens both with files running on Filemaker Server 8 and when I take the files off the server. Anyone else experience this? Anyone know why it is happeni…

I have multiple tables in use, each in their own file. There is ONE instance where data needs to overlap between tables, and there is no way around it. In table AAAA, I have a field named “AAAA_Incident Number” In table DDDD, I have a field named “DDDD_Incident Number” When a user creates a record in table AAAA, and enters something into field “AAAA_Incident Number”, I want that ENTIRE record to automatically propagate into table DDDD. Every field has an identically-named counterpart in the other table (but DDDD has a lot of other fields). A handful of users only have access to the file holding table AAAA, and certain record entries (those that h…

Hi All, I am not new to FM but not an expert by any means. In the past I have created FM databases to track sales and invoicing for tradeshows. I typically create a database a year per show. I want to create the database once and use the same one each show so i can track history, and other information i would like to keep in one place. I am starting from scratch and know this much. The main table will be the companies exhibiting, the problem i am having trying to figure out how to break down my tables. The main sale of the show is booth space, within this booth space will be unique booth numbers, sizes, prices, commissions, etc. but the payment schedule is three payment…

I’m a newbie and I am stuck. I have created a simple inventory database with a twist. I allow parts to put into packages. Ie. say you have part A, part B and part C, you might create part D which is comprised of some quantity of part A, B, and/or C or any combination thereof. I do this with a parent / child relationship and all is good. I have created a line item invoice database which allows me to select the parts from the inventory database and add them to the invoice – still nothing to fancy. I have 2 problems: 1. On my invoice, I would like to have an option to display what parts are included in a package. 2. I need to create a list of mater…

Hi, I am extremely new at this. Can a field lookup data from multiple tables. I have the following if it makes sense. 1. Client table 2. Service table 3. Jobs table OK The service table gets its information from the relationship from the client table (via Client#)creating a service #. The jobs gets its information from the service table when the service # is entered. Creating its own job# Can I get the same information from the Client table that the service template gets, into the job table with out entering a service# but by entering a client #. ******* I hope that makes sense

Ok, I've got a dented spot on my wall from hitting my head on this one. I had a solution hosted on 8 with 8.5 clients. It was running fantastically. We really wanted to lose one of our plugins and take advantage of the external data source thing. So we upgraded to 9, I made a bunch of changes to the UI and schema to implement the external data sources into the solution. It's now being hosted on FMPS 9 and the clients are using 9.0v3. The problem: My first sign that something was wrong was that it took almost 10 seconds to create a blank record in the main table. That time ballooned to nearly 2 minutes in some cases. The next thing I noticed was that th…

I have really gone all around google, for 4 hours now..pleeeeease help. I have a Clients(client_id, name) table related to an Invoice(invoice_id, _client_id), which is related to Documents(_invoice_id, status, price) . Documents can have different status (paid, unpaid, etc) I need to add the price of all unpaid documents for a client...as simple as that... but I can't!! How would you do it??

I have been a proud user of Filemaker and, having upgraded to FM 9 (and Vista), I also want to use it for bookkeeping. I have a small business and am not looking for complicated tax issues, just simple bookkeeping. Are there any simple templates available? I am prepared to 'build' it myself. Thank you.

I am about to start using direct debit for my customers and I am trying to think how I could record this on my database. At the moment there is a Customer table that has a job table related to it that creates a cost figure and I also have a related payment table that records income against each customer. And from these two figures I have a running balance. This works well, but these are only one off payments. Ideally I would want to enter direct debit information once with a day of the month for the payment, number of payments and start date. Then of the day of a relevant debit the database would produce a list of debits to be made that day and debt the amount from the am…

Im managing a product list table 1) partnumber, price table 2) partnumber, price Main table) Partnumber, description, price etc In my main table, i create a relationship between partnumbers table 1 & 2, and I use auto lookup according to... oops, can only choose one of the to relationships. How can I relate to both of them and make the price from both tables end up in my main table price column? A note: there are no price duplicates between table 1 & 2

I recently responded to a post by Bluearrow Topic #191849 re "Last available related record in a one to many relationship" I am soon to come into a very similar situation in my own development requirements: That is to say: I have the following relationships: One client may have many projects One project may have many cost centres One cost centre may have many tasks One task may have many timesheet entries Periodically, I want to raise invoices to my clients for the time spent on their affairs (potentially spanning multiple projects etc). The invoice will normally be for the total time spent on their affairs based on work we ha…

I have 3 different databases holding customer names and addresses. Is there a way to add a relationship that enables a search that finds out what database a customer is in? For example, the databases are Company A, Company B, and Company C and I want to see what database(s) Joe Smith is in. Right now I have the databases connected in a relationship through the name (First, middle, and last); it recognizes if the name exists in the other database. I was thinking there might be a way that I could create another database and link the 3 to that one and have the fields in the new database just point to the corresponding fields in the other databases for the sea…

In the costing database I'm building, we will add inventory items together into a catalog item to calculate the total cost. Right now, when I enter the item code, it looks up the description, weight, and cost. Problem is, some employees who are less familiar with the cryptic item codes will be doing some of this costing also. Is it possible to have either the item code or the description do the lookup? Ideally, I'd like to have the description field be a drop-down list of the items available for it to look up (I have the item code field working this way).

Hi there! I'm new to FM and this forum and I need a little help. I'm developing a product catalog based on a template. I would like to know if it is possible to update de price of the products without changing every record manually. Thing is that the prices of the recources change on a weekly basis. I would like to have a seperate window (table) where I can fill in these changes and then update the whole product list with single click or even better, automatically. Is this possible? Thx in advance

I am completely new to FileMaker so my apologies if this is a silly question, but I am stuck at the moment. My question is this, we would like to know, if possible, how one can make a header account, followed by sub-accounts, and then produce a meaningful report. To give you an example: We have a client, Footlocker. This is our Header Account and here we would find information about Footlocker’s Head Office Address, Total Revenues, Total Volume, etc. However, we would also like to track all of the Footlocker stores in Europe. So underneath the Header Account, we would have a listing for all (example) 500 Footlocker stores across Europe. How do we do…

I am trouble trying to use a dynamic multi-key relationship to display data in a portal From the Main Table the User selects a name from the pull-down value list. The listFIELDcalc is the multi-key field to a portal from the Incoming Data Table. Is it possible to have a dynamic multi-key field from a calculation field. If so what is the best way to do this? NOTE: The incoming data comes from an external source and does not have a name field TESTdb1.fp7.zip

Hey there, I'm running FileMaker Pro 8.0v1. I've got a database of products that I've been maintaining for some time now. Each product is linked from an Item Master (IM) layout to ingredients, qualities, display layouts, etc. by a primary key. Lately, with one item, the IM data has stopped appearing. When I search the IM layout for the primary key (or any other piece of data related to this item), the fields displayed on one record appear empty and cannot be edited. I can't even see data in the field that I searched to find the record. Looking at a field linked to this record from any other layout, it appears empty and cannot be edited. I've created a new record fo…

Absolute newby, trying to get to grips with RDBS and FMP v9. I read that one feature of relationships is to avoid data duplication, but I'm clearly misunderstanding how this works. I set up a very simple FMP file as a learning example, but I can't get it to work as I expecetd: Two tables, each has an auto enter serial number field as I read this is 'best practice'. Company: CompanyID (auto enter serial) CompanyName (text) CompanyTown (text) People: PeopleID (auto enter serial) FirstName (text) LastName (text) CompanyID.fk (number) I defined a match key relationship on CompanyID to CompanyID.fk …

I need some further advice on how to set up a database whereby the entry for each animal whether it is a father, mother or offspring would show any relationships eg. the entry for an offspring would show the mother and father and the entry for a grown-up would show all the offsprings. At the same time the reporting would be able to produce the total inventory of animals.

Hi, I am just starting out with FMP and am at the stage where I'm not sure how much I don't understand! Can anyone could help me with understanding how to produce a layout which comprises several fields which are enterable and contain a number, so that when the number is entered info from the corresponding record in a related file is shown next to the entered field. This is for several records at once. I want to be able to type in the left hand field (eg T0800) and when I tab out of it I would like the right hand fields to displayed info from the related record (No buttons wanted). I've included an image of what I'm trying to achieve. I don;t know if: this is easy/har…

Ok, I have 2 databases linked together. One is contacts the other investigations. I want to have a list in investigations of "Last Name, First Name, Company, Email, Phone" to show up if in contacts the field "case number" is the same as in investigations. IE: If the case number is the same in both databases, then those fields named, will show up in a list/portal in investigations. They are both related already. Any advice?? Thanks

I am setting up a program for a client who plows snow. He may plow snow more than once for a customer before he gets paid. In other words, he may plow snow on three separate days before he gets paid. The payment will cover all three days. How can I setup an invoice showing separate days and one payment covering all the days. Also he has a customer who trades dog grooming for plow services, how is the best way to show payment for this service.

I know that if the data is the same then it should go in the same table. But I've hit a situation where I have (gone ahead and) designed several tables. And now I'm feeling quite guilty and I would like to get a reality check from everyone. I have a table which acts like a gate-keeper (Router) between a SQL table and our FM solution. All it contains is the SQL unique ID, RouterID, CreationDate, ModificationDate and Status. It's sole purpose is to hold the two pieces together so they never get lost. The RouterID is passed into our solution so we always know exactly which SQL record any given record came from. This Router also tells me NOT to allow exact duplicat…

Hi. I want to create a button that opens one layout from another layout. They each have their own tables but are related. I have found the open layout command which works great but I want to be able to open the related record to the one that is already open. Table one has a key field and table two has a foreign key related to table one's key field.

Sorry this is probably a really stupid question but I have just started using Filemaker having always used Access in the past and I am just starting to get to grips with it. I have a database with two tables, Companies and Jobs. Each table has a key field which is an auto number Companies ID and Jobs ID respectively. The Jobs table also has a foreign key of Companies ID which is connected in a one to many relationship to the field of the same name in the Companies table. What I would like to do is open the jobs form from the companies form so that it automatically creates a new record with the Companies ID field in the Job table populated by the relev…

Hi, I have a database I am trying to create using a load of images. Firstly I imported the image as a refrence to the file it's self. I would like another databse to have a relastionship with the image database and allow the user to choose an image to import however I cannot get past the relationship stage as it seems you cannot define a relationship to a container field. Is there any way around this ?

Hi Guys. newbie here. Have simple plane load databsae with 1 record per person for every load. Once my staff have filled in the aircraft flight time for 1 record on load 1 how can i hide or fill in (checkbox etc)the next fields for aircraft time flight time in the records from the same load? regards Col

Years ago I created a multi-file, related solution in FMP 6 and have been using it happily since. I have decided to upgrade to 9 and have noticed that 9 has tables. I am confused about this. Do, or can tables take the place of related files? In other words can I redesign the system so that I have all functionality in one database? I am an intermediate user. Thanks for your help

I am working on a database to calculate costs for our products. My issue is connecting the item costing sheet to both the catalog year info and the costing details. The tables in question: CatalogItem (holds all the costing info for that particular catalog item) CatalogItemYear (holds the year and retail price of the catalog item so I can store previous year's costs) CatalogItemDetails (holds the various parts that make up the catalog item). In my layout, CatalogItemYear and CatalogItemDetails are portals. CatalogItemDetails pulls up the correct information but when I create a new CatalogItem and change the year, the info from CatalogItemDetails does n…

Hi, I've a layout in list view, 2 columns, sorted to the "sale of this month" column by decreasing order. Product Name Sale of this month Product Y Product Z Product A 10 Product C 7 Sale of this month is in fact a related field to an order table and qte, so when there's no orders there's no row with that product sku and hence no quantity = no related field. So the relation returns nothing. The problem here is that fmp treats the void values as if there were > to any other number. Is that normal. How to solve this, without modifying the relation, the related table, and w…

Hi all. I have a database that has a structure of Project > Task > Task Funding. The Task Funding can have multiple records per year for a given task, and a project can have multiple tasks I need to show a portal on the Project Level that would show a rollup of all the task funding amounts for related tasks by year. I know how to rollup the funding years within a task (using a calculated key), but do not know how to show a year rollup across tasks for the project. Can anyone please help? i have included a simple sample file. thank you! project.zip

I'm creating a inventory/orders database. As prices are different for each distributer, I'd like to select the distributer first on creating the order, then have the database look up the price for that item for that distributer. I've attached what I've got so far. Thoughts? Thanks, Matt scg_inventory_relational_pricing.zip

Hi All, I have a database with 3 tables, contact, sales, and alt sales. I have a separate database for pricing. I have to send a check out depending on our sales collected. It can be up to 4 checks per month. I am trying to create either another database or table to tie into the sales to do this but not having any luck. I have the data broken down by month and want to be able to show the check sent in relation to the sale. Any help would be appreciated. Thank you!

This is a two part question. 1. I'd like to design a layout where the contact information from a seller table and buyer table displayed at the top side by side and the realted property information (with several tabs) at the bottom. Is this possible (if so can search through the various seller and buyer records while keeping the property record the same) 2. I'd like to create a button that would allow the the current seller record address information to be auto populated into the current property record. And antoher button that would connect the current buyer record to the current property record. That is of course if the answer to question 1 is yes. and if ye…

Has anyone ever had their entire relationship graph disappear? My hosted FM 8 file (Server 7 Adv), just this one out of 15, has all the records showing in define database but the layouts are unusable (table missing) since I have absolutely no table occurrences anymore! Can I restore the graph from a back up? It is a day old back up so I would rather keep all my current data and just restore the relationships. Help!

I'm new to this so please forgive me if this is in the wrong place. I need to create an extensive relational database. I am a real estate investor who needs to create "packages" for sellers, buyers, lenders and title companies. Each groupd will have a different set of forms (some of the forms will be common among all groups) So there are 6 different entities and about 42 "forms". The six entities will require a selection from the "forms" And each form will will have some information from other forms as well as information from the 6 entities. So if you understand all that, the question is, is it better to create one database with 48 tables or 48 databases. The ma…

I've got a basic understanding of how a relational database works with primary keys etc. I've got an issue that is bugging me and there must be a solution I'm missing. I want to build a database for a university department. To start off with, it will contain people (faculty, staff, grad students, undergrad students) rooms, computers, programs etc. I want to associate rooms and computers with people, but only SOME people - faculty, staff, or grad students, NOT undergrads. Same with rooms. Basically, I want to limit the drop down lists. And undergrads and grads, they should have faculty advisers, but I need the drop down lists to only show faculty. …

New to the forum but have been using FMP for years. Please advise if this posting should be located elsewhere in the forum. Thanks in advance for any help offered. I am building a DB to process registrations (200 records) for an event with activities over 4 days. I need some help with how to design the necessary tables to make it do what it needs to do. Registrants (Winners) can optionally bring one Guest and both Winner and Guest will register for one activity each from a choice of 20 events offered each of the 4 days. Each of the 20 daily activity choices needs to have its own inventory maximum. As registrations are processed, the inventory will n…

Hi, I've 2 (possibly n) languages. For each languages I've 3 possibly n' wordd to translate That's an example to illustrate the point, do not think about the best way to do translations please. I want to display to the translator the following portal. French Word 1 Translation? French Word 2 Translation? French Word 3 Translation? Dutch Word 1 Translation? Dutch Word 2 Translation? Dutch Word 3 Translation? the "Translation?" field is where the translator would put the translation. I've 4 table The "main table" one upon which the portal will be displayed The "Language tables"containing language id / lang…

Hello all, I have an existing database that I need to add some functionality to. Specifically, I'd like the user to be able to ask a question or assign a task to any record in any table. So, for instance, they could ask a question about a particular quote or assign a task to a file saying 'please re-send,' etc... I'm basically creating a task management system. A simplified entity-relationship diagram is attached. On the right hand side of the diagram you can see the group of tables that I think I want to use. A project is just a series of tasks, questions, notes assigned to particular people that sometimes results in the creation of a product. Th…

Ok Relationship gurus. What I am trying to do is to create a selection layout that displays all the parts in our inventory that apply to a specific car/truck. Right now I have two fields in the "Sales Orders" table 'Year' and 'Make'. The user will select one choice from each of these. I also have two fields 'Year' and 'Make' in my 'Inventory' table these fields have multiple values in them each one seperated by a carriage return. How do I create a layout that shows what parts match to the fields in my "Sales Order" table Thanks in advance

I currently use an If statement that checks for the last error of 101 to detect if there are no related records before continuing in a script. Is there a better way to test for no related records in a script? I currently do this: Go to Related Record [show only related records; From table: ...] If [Get (LastError) = 101] Exit Script End If Continue on with script Kind of a basic question, but checking for a general error number of 101 is kind of risky. Thanks.

I have created a database that manages trips for a small jump airline. They charge a mileage rate, airport fee, wait time, etc. I would like to put these into a customer editable area so they can change these as needed. I have tried to use globals, but the values appear to change for some unknown reason. I then tried to set up an unrelated table, but I get in the fields. I also tried to create a separate database with 1 table and 1 record (containing all the fields/values), set up the file reference, but I can't select the table/fields in the script editor. I would be happy to use any of the above methods if they worked consistently. I've tried to run script debugge…

Hi All, I have Job there are about 9 status of a job and when ever the status of the Job is updated i need to store the date. I am attaching my idea of layout for review and i would really appreciate it if anyone can help me out here on how to desgin table and layout. Thank you in advance. Mitesh

Using FileMaker pro 6 I have a solution that has 20 plus files in a folder however the main startup file is outside the folder... I now want to get it inside the folder ( with the other files). Q: How best can I rework this solution and place the start-up file inside the main folder - and easily re-link the many relationships? As an example: I tried fooling around with this - closed the files - then moved the start-up file into the main folder - then attempted to re-link manually in the relationships window. I reset the links on one file - tested it - and it worked - then quit the solution. When I opened it again - the links did NOT hold - and went…

I want to use a list of images from one database as a value list in another database. The list in the first db needs to be able to display more than a single image, so I have the two linked via a join table, and use a portal to display the image list. My problem is that I don't want to add records to the image list each time I use one of the values in the portal. I can't figure out how to JUST use the values, and not create a new record each time I choose one of the images. Using the image database as a simple value list in a repeating table doesn't work, as choosing one value populates the other values with the same name as many times as it is chosen. More deta…

Basically, what I would like, is to get help or guidance (or a template to start from, if any exist), with respect to building my Data Structure and come up with the appropriate 'strategy' to build my Entity-Relationship Diagram for a small restaurant reservations system. Ultimately, I want to have a 'Daily Floor view' (see pic.1), where I would see all my tables for any single day (back and next arrows to flip through days), the tables being represented by small portals that simply show the time and last name of any given reservation. Clicking a name would lead to the reservation's details (pic.2). I'm following the VTC FileMaker modules right now, and I would …

Hi, When User opens the File, the Current Users record should display. What am I doing wrong in the Relation? Regards Ron

Is it possible to enter a record into a "master table" and have that record automatically inserted into other tables based on flagging specific fields? For example, if I flag the field foo and foo2 in the master table, a record is automatically added into my foo table and into my foo2 table. I used to use FMP alot but have not since version 5.5 or thereabouts.

I need help please, this is driving me mad. I have this issue in many tables that are related. I have a "contacts table" with field Full_Name (A calc to get: Prefix, First name, Last name etc.) and a "schedule table" with field Staff_Name that looks up the values from "Contacts table" Full_Name. Once Staff_Name has been entered in the "Schedule table" (via drop down) if I change the details of that name in the "Contact Table" (spelling for example) how do I get all the records that have that Full_Name in the "Schedule Table" to change to the changed entry? Thanks in advance for the help!

i've got a table of sales people, a table of clients, and a table of sales (jobs). when the sales people enter new jobs, they have view only on their clients, and view only on their sales. when they select the client, the sales person is supposed to automatically be inserted (for when somebody else enters jobs in for the sales people). well, it's failing to lookup the sales rep from the viewable client table. is it because they can't view/modify the job before they select the client? because when they select the client, it does add that to the job, but just fails to lookup the rest of the info. so a job without view permissions can be modified if it's a new job?

Hello, I'm a Cub Scout Leader, and am evaluating FMPro for use in managing my paperwork (and ideally creating a solution I can distribute to others). I've created a basic DB (attached), and I'd very much appreciate any feedback on where I might be going right or wrong. In particular, one thing I'm struggling with is managing the relationship between people records: some people are Youth Members (who must have one or more Guardians), and some others are Guardians. To differentiate between the two I've created a 'boolean' field (YouthMember). I have a List of "people" (PeopleFullNames) which I'd rather have as "people who are guardians" (GuardiansFullName) (i.e. …

I need a way to be able to add notes to my records. I also want to have each note get a time stamp so that I can look at my job ticket and see that a note has been posted in the last 24 hrs. I am trying to set up a new layout linked via a job number that I can click to and see all of the notes listed for that job. Any ideas?

Please excuse what may be a very stupid question. I have created a db with 2 tables of respectively 198 and 150 different fields. After doing all this work, I realize that it would be better to have everything in a single table. Is there any way to merge the two tables, or transfer all the fields from one table into the second? Thank you for your help. Christian

Hi everybody! I'm trying to create a relationship that's based on partial or imperfect matches, and I'm wondering if it's even possible. In a typical relationship between table A and table B, entries from each table are related when the matching fields in each entry are exactly the same. But what if I want the entries to be related if the text of one matching field exists in the text of the other, even if the fields are exactly identical? Here's an example... Let's say I have two tables, Disney Movies and Disney Characters. Let's say I first fill out entries in the Characters database, including the movie that each character is in. In most cases, a characte…

I am trying to set up a one to many relationship database with and client table and an animal table. Can anyone help me design the fields so it will join? After I get the tables joined I will be able to add what fields I need. I have been working on it and have it working somewhat. I noticed on some examples that they have fields named _kp_personid and _kf_familyId but I am not sure if the_kp_ or _kf_ actual mean anything or if it is just the way it was named. Thanks for anyhelp I can get....

I have imported records from an older Filemaker database into our current Contacts database. The current DB has separate tables for clients (companies) and contacts (individuals) and these tables are related via the client name field. For some reason none of the imported records are recognizing the relationship. This DB is a modified version of one of the templates that comes with FMPro. There is a field named zk_F_clientID.t that is used to calculate a value used in the relationship (I'm not quite sure how it works) but it appears to not be working on the imported records. Thanks in advance for any help you can provide

I am trying to create a reminder system. I would like all the reminders to be in a separate table so I can generate reports (i.e find all reminders due next week). The reminders have different start dates, and different lengths so for example Reminder A = StartDate1 - 1 months Reminder B = StartDate1 – 1 week Reminder C = StartDate2 – 3 months There will also be "custom" reminders where they can add their own date and description. Etc. I have 3 tables Data (this is the table that is the "main" data – "StartDate" is in this table) RemindersType (the table that holds all the various Types of reminders i.e SerialID, ReminderName, ReminderLength…

Ok I have a table "inventory" which contains information like price, part number, and two fields "year" and "make". Most of the parts work for multiple years and even multiple makes. What I want to do is to create an "Orders" layout that automagically shows all the parts we sell for the year and make of the car/truck that the user enters(there will probably be no more than 20 parts in this list). The user can then add a quantity to each line item to add it to the order. I have thought about using the "year" and "make" fields as multiple match fields but that would require selecting each year and make from a popup menu in the inventory table??? Another thing to keep i…

I recently installed Brian Dunning's ChartMaker Pro 7 to add some pie charts and bar graphs to my database, but I am having a lot of trouble putting together a calculation to pull my data together. I have drop down menu which contains the following selection: active, non-active, new, routing and on-hold. I would like to be able to create a pie chart the shows the number of jobs in the database for each. Is there a simple calculation to do this? I really appreciate your help.

To give some background on myself, I am completely new to Filemaker. I have some experience with design in Access. I have no idea how to create scripts and I'm hoping I won't need one, but I'll try if necessary. I have a very simple database with only 2 tables: Organizations and Documents. An organization can have a large number of documents. The documents have a lot of attributes (about 15), while the organizations have very few attributes. I would like to be able to go into the organizations file and view the related documents in a portal (which I was able to do successfully), but I would also like to be able to add new documents. Ideally, I would like to add…

Thanks in advance for your advice! I really appreciate it. Here's the scenario: I am building a database of the families at our church. I've started with two tables. The first table is a list of youth and their contact information (address, email, phone, school, etc.). The second is a list of individual families (mom name, dad name, address, phone, and kids, etc.) What I would like to do is be able to do is have the Youth table autofill information from the youth's information in the Family table. I would settle for it being the other way around. I can do it just fine when a family has only one youth. However, some families have more than one youth (which I'…

OK, everybody knows that a Go To Related Records script step should always be checked in order to know if it was successful or not... but I couldn't expect this to become so tricky if GTRR is set up to show related records only, with a 'Match foundset' option. This file illustrates unconsistancies of GTRR behavior and error code returned. More than a bug report, I think one should not ignore these results. I wonder how many bugs I ran into just because of that... Hope this helps. GTRRbugReport.fp7.zip

I hope I can ask this question in a manner that makes sense as I am brand new to FM. I have 2 existing databases. I am trying to modify them so they will communicate with each other. When I enter a number in a specific field, I need that same data to be entered into a coresponding field in a different database. Is this even possible? Thanks, in advance, for your effort.