Relationships

Post deleted by Kicker3

Hi, I am trying to relate two tables, one contains stock data and is my 'main table'. The other contains operator details. They are linked by the field 'OperatorName' which is a global field in stock and a regular field in Operators (as there are many individual operator records). The Global operator field in my stock database gets its data from a Get(AccountName)startup script. I am using this relationship to return a value called 'OrderCounter' - a value unique to each Operator. However, when there are no records found in my Stock table, my button - 'SetField (Operators::OrderCounter = Operators::OrderCounter + 1' - doesn't update the counter. This is…

I have set up a calculation to generate a unique code for a contact (the first three letters of their first name and the first three letters of their second name). I want to use this field as a match field to relate to other tables but I can't have a calculation field matched with a text field, the related data isn't correct. Any help appreciated!

Hello everybody, My problem is as follows: I have three tables named (paper, journalist, join) and a many to many relationship. The join table is a join between the two main tables. It is now too difficult to add something to or delete something from the tables. Each time that the user want to add sth. he should memorize the key of the wanted record in paper table and the key of the wanted record in journalist table and in order to make a relation between these two records he should make a new record in the join table and add the keys to that record, the information of both records from the main tables come in to this new record in the join table via cal. fiel…

If I have multiple tables that each have records of courses in them (from different subjects) and each record has a field for the course name ("Course"), and I want to create a value list that has the names of all courses from all subjects, shouldn't a cartesian join do this? What I tried to do is below, but it doesn't work: Admin::CourseNames x Subject1::Course as one relationship and: Admin::CourseNames x Subject2::Course as another relationship and then just defined the value list to take values from Admin::CourseNames. Any ideas on what I'm doing wrong?

I'm trying to set up a database with at least two relational databases. In one I would like to dump information regarding classes being taught in our department (i.e. number of credit hours, time of day, which classroom, who is the inrtuctor etc.) and in the other database information about each instructor (i.e. tenure?, personal information, research projects etc.). Eventually I would like to create a report with specific personal information, as well as a list of each class that each professor is teaching for a given semester. (which is why I would like the classes in a separate file, one that could be emptied and refilled each term) I would use a portal, but some pr…

I have two databases: Behavior & Classes In the "Behavior" database I have a text field entitled Consequence where the staff chooses one of four possible consequences from a drop down: Time Out, Dismissal, EDR or Assessment. In the "Classes" database I have four number fields that correspond to the consequences above. I would like for those fields to tally the total number of each consequence for each student. Any thoughts? Ben Feingold Dearborn Academy Arlington, MA

I'm having trouble with a look-up field. I have correctly made the relation, and the lookup works, except for one field. I am trying to put the e-mail address in a field called "eMail" in the master, from a field named "street" in the original/lookup database. (whatever it is called). However, when I make it go (for lack of a better phrase) by entering the last name, things such as address and first name show up but the e-mail address does not. >It works to place a different original field in the "eMail" field in the master. >It does not work to create another field in the original named "eMail" and try that. >It does not work to place a regular text str…

I have an FM7 file I'm trying to convert from 2 FM6, turning 2 files into 2 tables in one file. I have 2 tables, PayrollEmployees and PayrollLineItems. I have a self-join relationship called InFoundSet, cOne (of PayrollLineItems, a constant = 1) matched to boolInFoundSet (a number field toggled by script between 1 and 0) so the InFoundSet relationship is cOne = boolInFoundSet, both fields from the PayrollLineItems table. On a layout in the context of PayrollLineItems, I have a portal showing records from InFoundSet. There are 2 fields in a portal row, InFoundSet::cdEventDate and InFoundSet::ctEventName. This portal seems to show the correct number of rows (i…

If a relation is added to a database, will it slow the database when the layout (that depends on the relation) is not the active layout? Thanks! Phil

Hi all--I am getting unexpected behavior in a portal. I have an inconvenient work-around, but I'd rather understand what is happening. The native key is a single line text calculation, and the foreign key is a multi-line text calculation. I see data in the portal, but performing a find returns no records found, whether I search for specific data or "> 0" or empty. I built test files duplicating the fields and relationships, and the problem persists. When I change the NATIVE key to a text field, the problem goes away. Any ideas what I'm missing? The work-around is to regularly update the native text field with the calculated results of the native key, but that's counter…

How can i exchange the value of a field between two tables that have meny to many relationship? Thanks,

What is the canonical relationship technique for accessing global in another database? I have a table that has several global fields that I would like to access from the layout of another table (I'm still on FMP6). There is no other relationship between these two tables. Some kind of artificial relationship has to be constructed. What is the usual technique? I tried relating between globals fields but that doesn't seem to work. At least, I get a warning that says globals cannot be indexed so the relationship won't work. So, what's the correct technique to use here?

I have a db with 2 related files. In file A, I have a portal. In the Portal row, if I assign a button the script step of going to the related record in file B using the 'Format' -> 'Button' -> 'Go to related record' from the menu, this works fine. However, if I assign the same button a script, even a script that has the identical step 'Go to related record', clicking the button does no do anything. Thank you in advance.

Hi all, I have 2 separate files in my billing system: 1. Facture (invoice, taxes that I charge to my customer, total, customer ID, ect.) 2. Deduction (my expense by categorie, taxes that I pay on each expense, and taxes total) In my "facture file (1)" , I want import the total taxes summary of the "deduction file (2)" with define relationship fonction. I want import "grandTotalTPS" field: (un.Total TPS + deux.Total TPS + trois.Total TPS + quatre.Total TPS+ cinq.Total TPS + six.Total TPS + sept.Total TPS + huit.Total TPS + neuf.Total TPS + dix.Total TPS + onze.Total TPS) And "un.Total TPS" is taxes a summary If I can't index a summary…

Hi everyone- I've dabbled in FileMaker for a few years and now I'm starting to make my first serious database. What I'm making is an order entry database. It's actually rather simple with the exception of the issue I'm stumped on. The background info: I have a single database file. In it are two tables. The first table has an order number, customer name, item, itemqty and itempicture. What should happen is when you click on the item field, a pop down list comes up, you select the name of the item they're ordering and then the picture of the item pops up into the itempicture container. All of this works just fine. The second table are the item pictures.…

OK, haven't done much FM lately so perhaps my brain is rusting up. What I want is a relationship between a global text field and a regular text field. I know globals aren't indexed, but I still thought they could be used as the left hand side of a relationship. But it isn't working. I've attached a quick example. gTest = "Hello". When Test = "Hello" the relationship should match and the text tag "Working!" should show up in the 1-row portal. But, sadly, it isn't working at all. "Working!" shows up on every record. What am I screwing up? The only way I can get this to work is to set gTest to be non-global with "Hello" as auto-entered text. Thanks…

I have FileMaker 5.5 dilemma. I have an old database system that I need to merge/import into a new database system. The basic issue is that the older database uses a different relationship than the new system. The relationship is one-to-many. When a record is created/imported into the new system, an auto ID is created called 'IDWebJobs'. The old relationship is based on a field called 'SpecCode'. The question is when I import the old records into the new system and the 'IDWebJobs' field is auto-populated, how do I change the other related database to match these new ID's without manually entering in the new ID's? Is there some type of script/calculation that I can cr…

Context: Matchfield in a related file that is the same across all records to pull all records from one T.O. to another. One can do this with a text field formated to auto enter a data entry (eg: A) or a calc field with the calculation "a", and both set to save and index. Question: is there an inherant advantage of one over the other. To my brain the calc field is nice in that if I decide to change the contents (for whatever reason), I don't have to do a find and replace as I would have to for the data auto entry. Analogus issue: in the main file, I can use a global or calc field as a match field that is the same regardless of what record I am in but unique to each…

I want to be able to allow people to enter comments into a comments table through a portal. However we have many different types of Comments. Lets say Service Comments, General Comments, and Sales Comments. So, I want 3 different layouts showing records from a table called Case, with portal records showing comments. 1 Layout shows portal records of Service Comments, the other General Comments, and the other Sales Comments. What is the best table structure to implement that? Do I need a different table for each type of comment, or 1 comment table with a 'comment type' field?

I have a problem with passing data between 2 tables. The database is a single file with no external relationships between any other files. What I have is a Screen to enter patient information and one of the fields is a Medical Record number (called MR Number PTINFO)and it is define in its own table. I have another screen called "insurance information" and it has its own table with a field called "MR Number INS" (To distinguish the two differently). When I add a new patient or retrieve a patient from the patient information screen, I need it to refect the data related in the insurance table as well. I have created a relationship between the two tables in the r…

Hi, Posting in "my" own Forum.... Converting an old Options Hierarchy system. I'm wondering which design is better. Solution 1 : Basically Table A relates to Table B through these possible keys - In Table A : cKey1 = Field A & Field B cKey2 = Field A & Field B & Field C cKey3 = Field A & Field B & Field C & Field D - In Table B : cHierarchyOptionKey = Field A & Field B & Field C & Field D Each record in Table B has a unique separated combination stored in Table B Record#1 - Field A:A10 | Field B:B10 | Field C:Empty | Field D:Empty | Record#2 - Field A:A10 | Field B:B10 | Field C:C10 |…

I'm putting together a master database for a fencing contractor, and everything is working fine, except one teeny piece. When the office admin starts a new job, she clicks a "New Project" button. A serial number is assigned to the project, which then drives the ability to Generate Quote, Generate Purchase Order, and Generate Invoice, amongst other utilities. A job may have pieces from several different vendors. My idea was to feed the PO form from the Quote, and then the office admin would simply click a "Vendor" drop-down. Each vendor who had material in the quote would display, and each vendor's materials would display once that vendor was selected. Unfortunately, …

This should be basic, but I'm new and have read a lot of the posts, but haven't found the answer. I have 2 files: Airport and FBO, with lots of fields in each. They both share an Airport ID which is unique. Each FBO at that airport will have a unique name. I have 3 buttons which point to 3 different layouts for updating different sections of the airport data, and I have 2 buttons for pointing to layouts which update the FBO data. There can be many FBOs at an airport and I need a way to select an FBO name for that airport from a drop-down list, then it needs to display info about that FBO on the two layouts. From what I've seen of portals, they are usually a …

I am trying to create a self join relation. Setup: Table Occurences: "Table", "Table SelfJoin" and "Table SelfJoin Global" Fields: marked_b = number can be 0 or 1 constant_n = number auto enter set to 1 constant_gn = global number set to 1 Relationships (2) "Table" >> "Table SelfJoin", when marked_b = constant_n "Table" >> "Table SelfJoin Global", when marked_b = constant_gn Problem: Neither of these two relations work either they show all records or none. No matter if I create five records in "Table" and set the marked_b field to 1's and 0's. can anyone tell me what I am doing wrong, I have attached a sample f…

Hi everybody, I'm not sure I'm in the right topic, but anyway it looks for me like a relationship problem. Here he is. I've a calculation global field (I'm using FM7) in one DB. I would like the value of this field be used by another DB in a script. But, in the second DB the value is always empty I don't understand ! It does not seem to be the relationship itself, 'cause when I change the global calculation field to a "normal" text field everything works fine, but I need the global calculation field. so....Help Thanx a lot

Hey FM'ers. Here's something I fall accross today, while studying some of the new Relationship concepts, and revisiting my current solution. I didn't totally finalized it, but before the concept grows in complexity (as usual with my messy brain),here it goes. Say, to give a concrete example, you have an Ordering db, where prices may be - The Catalog Price - A special net Price for given Customers - A temporary Promotional Price - A discount by Quantity Price So I established separate TO's to be linked to the Line Items Table. With v6, I used to test for valid relationships in the separate files (Promotions, SpecialNetPrices, Discounts, Ca…

Hello All, I have a layout with a portal consisting of a client_application_join; a list of applictions per client. Is it possible to get all of my application lists of every client into another layout? Thanks, Galen

I have a database with multiple files. There is a 'Members' file with each record containing details of an individual's membership of our organisation. There is a 'Worksites' file with each record containing information about the worksite that a member may be working at. There is a 'Staff Reps' File with each record detailing one of our staff members, who have responsibility for looking after one or more worksites and all the members working at that/those worksite(s). There are other files, similarly related to the Worksites file (eg Regions - the regions of the state where the worksite maybe located, Awards - the industrial awards that applies to all members working at …

Is there a way to set up a relationship that will match any record whose match field contains (but is not necessarily equal to) a search string? I have a database of 26K records, making up our mailing list, there are a number of people and companies we do not want to send mail to, and need some way to designate that. Easy enough, the problem is that the people we get our data from do not always enter Company names the same way. For example a company "Housing Authority" can be entered as First=Housing, last=Authority, or First="Housing Auth" last="", (And they're always coming up with new ways to do this. Is there some way to set it up so that a match field containin…

Hello, I have a Line Items file that is used for a portal in my order form. Whenever you type in an item number, it performs a lookup for the price, description and Quantity on hand. What I can't figure out is how I could make a field in order form that would recognize that the quantity on hand of one of the items (which would be viewed from the portal)is less than (2), indicating that there is a possible out of stock. I thought about sorting the relationship by Quantity on Hand but then it throws off all my item numbers, as that is what the current sort is set for. Any ideas?

Ok i'm using FileMaker 7 I have made one table for Customer details with a customer ref number An invoice table that has customer ref no, invoice no etc... An invoice_line table that contains the "products" What i want to happen is :- in the customer table, it to show customer details, and ALL invoice related to them, and that you can click a link next to each invoice number to view that invoice. In the invoice table to have that allow me to enter the invoice_line items add them together and then allow more items to be added to invoice. I've made the database, but it does not allow me to create an invoice though the portal , i know i'm doing somet…

Hi all, I have three files - call them A, B and C. C contains just four fields. Two text fields, a calculation field that is the concatenation of the first two text fields and a container with GIFs in it. B contains a whole heap of data. A has a relationship with B (this works fine). I then have a relationship between a calculated text field in A and the concatenated field in C in order to pull the GIFs from file C. Bizarrely for most values of A this works fine, but for some it does not! For some of the values in the calculated text field in A, it brings up the wrong GIF. I know that it is the matching of records that is at fault because I …

I am trying to ctreate a relationship with two criteria. The first criteria works fine for me, but the second one does not. I am trying to match a classification field in one table to a global field in the other table (Because I want the relationship to pull only those people classified as "X" not y,z, etc...) I read on another post that the fields in a relationship must be indexed, which a global is not. is there anyway to create a relationship like this? Example: person 1 belongs to ABC corp, and is classified as X person 2 belings to ABC corp and is classified as Y. I already have it set to match company to company, but I can not get it to pull just …

I'm creating a project management database. I'm having trouble defining relationships between two of the tables. The "project" table tracks projects, and the "contact" table tracks contacts & companies. It's easy to set one individual as the primary contact for a project, but my client wants multiple contacts from different companies to be listed on each project card. How do I relate otherwise unrelated contacts by project and have them appear in a portal. Ie, anyone of the contacts who had any hand in the project should be listed on the project entry. I can't get more than one contact to appear. Any help? Much appreciated.

I have a file with three related tables, A, B and C. The relationships between A and B, and B and C, are predicated on different fields, but for each new record in C, there's only one possible related record each from A and B. My layout for C contains fields from both A and B. When I create a new record in C, the B fields are always filled instantly, but the A fields frequently don't come in even when the record is committed. They always show up in the long run, but it sometimes requires closing and reopening the file. I've also found that if I start filling in one of the A fields in my C layout, it prompts the rest of the A fields to fill themselves. Is there a more…

I have a small problem that I need some help with. I have two tables and have joined them together in a relationship. I can pull information from the 2nd table without any problems and have it populate the fields in the first but what I want to do is; if it can't find the information then when I fill out the 1st form, I want that information to automatically go into the related table (2nd table). I saw that when I did this that there was checkbox that is suppose to allow this (Create a related record) but when I checked this, it still does not create the record in the 2nd table. Am I doing something wrong or does Filemaker 7 not support this and if they don't support this…

I am trying to write a calculation that shows overlap in time. I have a field for start time and one for end time. I need to identify where there is an overlap in these times when comparing records. I've been trying to use a self-join relationship and a Case statement. I

I have a list view which includes simple calcs that I can first sort by manufacturer (with up to 25 products, for example) and then limit that sort by further criterion -- I want to take the data from that last found set of records and create a new and permanant record in a new layout (in a new file, if necessary).

not quite sure about this one..... I am trying to have data in one file(File : (that will change occasionally, yet be necessary for each record in another file(File A)). I am not sure whether I should utilize a relationship or a portal as this is not something I have used before. Here is what I am trying to do: I need a school name, address, and policy # in file B(child) to be used in File A(Parent). There are similar fields in each file. any thoughts....

To filter the right information within a database I do this: Field A is compared with field B within the relationshipAB: Only records will be shown when A machtes B Field B is a calc.field from another File, that itself uses a realtionship to a third file for the calculation. This shows no result. When I replace the calculation field by a "normal" field and when I enter some possible values everything works fine. Help needed. thanks!

Folks, I have a multiple file database which I'm currently rewriting for use with FMP7. While doing so, I'm trying to improve the internal structure a little. Here's just one issue: Currently I'm using 24u Prefs+Globals Plug-In to hand over variables across the individual FM files WITHOUT the need for any relationship. Question: as the tables (which were previously individual files) are in FMP7 now all integrated within one (database) file, is there such a thing as a table-wide variable, so to say a global which I can retrieve from any table, regardless of any relationship???

I'm using FM6 for an inventory database. I've got it broken down into a few separate databases. Rooms: Building Room RoomID PC: Building Room RoomID Serial# etc Monitor: Building Room RoomID Serial# etc I've got everything related by RoomID and have a lookup created to pull the RoomID when a building and Room are entered. If I enter a new piece of equipment in the Room database, everything is ok but the Building and Room fileds are not populated in the PC or Monitor database. If I enter the equipment in it's respective database the room ID is not correct because in the look up it finds the same room number but for a differ…

Hi, I have one master database and one transaction database and I am using a realtionship to populate the field in the transaction dtaabase. Ideally whenever I import the excel file into transaction database all the records based on the relationship in the lookup fileld should be populated. Unfortunately sometimes the problem I face is not all the records are populated and when I click and relookup the relationship field, everything is populated. Not sure if others are also facing the similar problem, Would be great if you could suggest any possible reason for the problem (one way of course would be having a script which always relookup the relationship after impo…

Hi, In search mode, when no records are found, we can use the Get ( LastError )=401. But within a relationship with the Go To Related Records script? How can I know that no record is related ? It's weird, because if the user clicks on a button with the Go To Related records script, and that no record is shown, then nothing happen. Chris

I have two files. The first one is SITE.fp5, which has fileds "site_id, site_name", and the second one is PATIENT.fp5, which has fields "patient_id, patient_name, site_id". Basially, each PATIENT belongs to a SITE and each SITE may have zero to many PATIENT. Now I want to create a report on PATIENT.fp5 grouped by SITE. I first define a relationship to make the two "site_id" in the two files related. Everything works well except this: some site does NOT have any patient. How can I list those sites on the patient report?? My current report only show those sites with patients. Is there a way to do outer join in Filemaker pro? Your help would be bes…

Hello all, Please help me on this problem: I have an <PO file>, <daily order file>,< customer file>,<product file>-you could tell all these files are related (different files with relationships) <PO file> I input customer information, PO information, date, products ordered (by color, description, quantity, price, dated due <Daily order file> I don

Hello... I wanna to know the basic step in using the looked-up....FYI, b4 this, i using a unique key to make a relationship in other database.... so bad.... i must to remember the key.... it's not better to system... what i know.... looked-up function will be automatically add in the other db..... TQs...:-? [color:"blue"]

So, I've thought it weird how filemaker did relationships for a heavily normalized database since fm 5. Now I'm using FM7, and my client who uses the database as a bound solution is having troubles with line items switching invoices they belong to. I've gone through and added commit records to everyplace I can think where somethign get's changed, and yet they are still having line items switch to other invoices. My next step is to go through and remove all lookups but I'm having trouble with some lookups apparently being neccsary in some situations, so here's what I've got, maybe someone can propose a better way of doing it. This is an inherited application that…

I have two TOs, Home and PO. Home is a set of global fields, one is a calculation labeled gZero and has a calculated result of zero. In PO, I have a calculated field of zc_InvoiceUninvoiced, which is the amount used on the PO minus the amount already invoiced to give a value of what has not been invoiced. This was a quick and dirty way of tracking invoices without a full blown invoicing system. Anyway, the number that is displayed is like $450.00. I then have a relationship between Home and PO like gZero < zc_InvoiceUninvoiced. The problem is no records are every displayed in the portal. Are there any caveats when relating numbers or calculations or some such. …

I have three databases, one for locations, one for dates, one for events. When you add an event, you pull down all the locations, some predefined dates and then you add an event. I'm trying to make it such that if you pull down a date and a location that already has a record associated with it (i.e. what would be a second record at the same location on the same date), the first record would show up. Example: Earlier I told the database that Led Zep was playing on the 9th at the Forum. Today I create a new record and pull down the Forum (from the location list) and the 9th (from the date list). Can anyone think of a way for filemaker to take me to tha…

Is it possible to relate by an ID, but display by name when selecting the record to relate to? For example: Order related to Customer by CustomerID. When the user creates a new order I want them to be able to select from a drop down list the customer name that they are going to link the record to. But when they select the name, I would like it to store the ID related to the name, not the name. In other languages/development environments, it is possible to set the display/bind properties of a drop down, so that I could set it to name/id, and name would display, but the value is the number. In filemaker, I have found that you can show two fields at a time, for inst…

I have several Tables showing in portals in a customer file using IDCust as the key relating the two tables - for example: Contact using a relationship I call Cust.IDCust.Contacts which relates the table Cust to Contacts using IDCust as the Key Invoice using a relationship I call Cust.IDCust.InvH which relates the table Cust to InvH using IDCust as the Key These relationships work fine and show the related table fields in portals as expected. However when I try in a layout of the Contacts table to show the Customer Name - situated in the Cust table using the reverse related table Cust it does not work in all cases even though Cust is shown as a related ta…

Hello All, I built a database in Filemaker Developer and now I want to build my GUI as a seperate file. So far I have only referenced my database file with my GUI file. Do I have to recreate all of my tables in my GUI file that are in my database file? Not really sure if there is an easier way? I will also be making my join tables and global tables in my GUI file. Thank you!

I've got a database for my checking account like a regular ledger. I have a calculation called Amount that is Credit - Debit and a summary called Balance which is a running total of Amount. I want to know the balance at the end of each month to put in another db. So in this db I have a self-relationship of Month and Year for an EndDate calculation of Max(self-related db::Date). This correctly gives me the last date of any given month. And that's where I am completely stumped. No matter how I relate my new db to this checking db, I either get nothing at all, Credit - Debit for one particular date, or Credit - Debit for a particular month instead of the total bala…

hello out there is FMP land! I have a question about relating tables etc... I'm trying to build a database with related drop down menus. Say I have a series of drop down menus, the first has software makers listed (Adobe, Microsoft, etc...), then I pick, say Adobe. I want the next drop down menu to reflect only the products that Adobe makes (or each other software maker). The user guide doesn't go into very good detail on how to do this, it talks about relating tables (even has a tutorial), but I can't figure out how to do this drop don menu thing. Thanks in advance, Justin

I think I am missing something simple. Please help! I have two related databases: Contacts and Activities (One to Many). I will find a set of Activities (e.g. that occurred in a date range). I then wish to find the set of Contacts for these Activities. I can find them one at a time. But how to find the set, perhaps in a short script? Thank you in advance, Kay

Alrighty, this is the situation... I have a "clients.fp5" database and an "invoice.fp5" database. The client database shows a "portal" of each invoice that was created and linked by "client code". Now the thing is...certain invoices are to billed under a shared "contract code". This "invoice.fp5" database uses a number field in regards to the "total billing price" and a number based "contract code" field that is to be used only when an invoice is to be billed under a shared bill. I plan on adding an additional database called "contracts" and have it as a portal in the "clients.fp5" database. What I need to do is add the "total billing price" of the INVOICE r…

I have a numbered client table - starting at 1 - and an invoice entry form in which I choose a client number and then name and address data is filled in from the client table. This all works perfectly well except for client number 1 whose data is not displayed. If I change his number to anything larger it is displayed but I do not want to change a client ID at this time. Equally if I change another clients ID to 1 their details do not appear on the invoice form. Does anybody have any idea what is going on here?

I started learning FileMaker 6.0 yesterday. I have a field called Category that contains items such as Advertising, Repairs, Supplies, Utilities, Wages, etc. I have created a layout (Payroll) that displays only the Wages records. However, when I leave this layout and go back I have all my records, not just the Wages records. How do I save the Payroll layout so it only displays Wages records? I have a second layout (Purchases) in which I want everything except wages. I can't figure out how to do this.

Hi All, I'm new to filemaker and I've started with downloading some templates to see how the database works. I have a problem which I'm describing shortly: There are 2 tables A with fields ::id (numeric;unique) and :descr and table B with fields ::id (numeric;unique) and ::refAid (numeric) which is related to A with relation B::refAid = A::id. The layout in B is using pop-up list for field ::refAid to pick values from A. It could sound silly but I could not make the pop-up list to show the A::descr field (only) and to save A::id field in B::refAid. I try to hide all of the IDs from the layout (the users should not see them ) and to make all of the relations by n…

Does anyone know a way to access related fields in a calculation so values (other than the first matching value) can be retrieved? In a repeating field this can be done with the GetRepetition function. I'd like to be able to, for example, access the 7th related record meeting the relationship criteria without having to create a portal and a script to loop through the portal rows. I realize this is not a built in feature of filemaker but I'm wondering if there are any new tricks in FM7 or plugins which might enable this. Thanks...

Let me begin. To avoid all those nasty habbits of the standard portal i use valuelists and my own script based scrollbar. This technique has earlier been suggested in this forum, it works great. Now i have a question concerning how to sort in the portal. 1] I have a child database with the following fields Class Name Age RecordId 2] i have a parent database with a (global)field SearchClass. 3] I have a relation between SearchClass and Class in the child database. This relation is called Contacts. 4] i have a valuelist based on Contacts that contains the recordid of the records ; ContactsVL I have calculated field ; DisplContacts=valueListi…

Hi, I think I need a relation here, so I posted here, if not, please point me to the right forum. I'm trying to get better at FM and I came up with this little fun project for keeping movies that me and my mom own in a fm database. Here's what I am trying to do: I have a 2 Dropdown listboxes/Combo Boxes. The first dropdown contains movie ratings. Say R, PG, PG-13 and G. The second drop down contains a list of movies. The user picks from the first drop down a rating, say 'G'. In the next drop down, it's contents should contain only G rated movies. If the user picks PG rated movies, then only PG should be displayed. The user (actually one of my f…

I want to find out, from a script in DB1, if there's already a related record in DB2 and, if so, refuse to invoke an external script in DB2 to add a record. In addition, if there are two or more related records, I want to be able to put up an error message. The relationship is to a Customer database and is based on telephone number. What is the easiest way to find out how many related DB2 records there are to the current DB1 record? Thanks for your consideration, Chap

I'm trying to figure this out: I'm doing a search action by setting a key and then set a script to [show only related records; From table: "KeysA Data" , External , Using Layout: main::gLayoutnr] What happens is that the related fm7 file shows itself in the front. In fm6 that didn't happen. When I de-select 'Use external table's layouts' I get this error message: 'This operation could not be completed because this layout cannot display the result.' Pretty annoying, but maybe there's something I missed?

First FM project for my business, can't figure out the following: Have a case file which has information on each "case." Each case is divided into multiple parts (for example a,b, and c). The part file contains a text field and number of billing units for each part. (Case file and part file related by the case#.) I created a field that adds up the total billing units for all the parts in a case just by Sum(related field). And I can display the related text fields in a portal on the case file. However, I can't figure out how to get the text fields concatenated into one summary text field. I don't know if this matters, but I think I need it to not be done in …

Ok I am totally new to FM7 I am trying to get a relation ship with to files both have ID numbers , when I create the relationship thats all fine but when I type in the ID number nothing comes up on the related database which have the same feilds as the other one arrrrrrgoing arahhhhhh Does any one have a good web site with screen shots on how this is done or if you can direct me where I can get this info.

I've got a table "Master" with these fields: flight number, dest1, dest2, dest3, dest4. These fields would be used in a separate table "Market" filled with records of each combination of the destinations. I want to make fields that auto fill based on contents in dest1-4. They will be fields representing the number of combinations that can be made out of the destinations. Example, dest1 to dest2, dest1 to dest3, dest1 to dest4, dest2 to dest1, dest2 to dest3, dest2 to dest4...and so on. My goal is to get data from a flight that may have gone from point A-B-C and back to A. We have to record data in a "Market" table that reflects what was on there from A-B, A-C, …

i have a tricky question (at least in my mind) i have the following files: invoice, line items, line item options they relate as follows: line item options -> line items, line items -> invoice ------ now for the question?:? im trying to get a report or layout (on the invoice file) that will print as the following: -------------------------------------------------------------------------- someone.com (lineitem) ----design (lineitemoption) ----web space (lineitemoption) example.com (lineitem) ----monthly maintainance (lineitemoption) ------------------------------------------------------------------------------- …

Let's say record Parent1 is related to three records ChildA, ChildB, and ChildC. (If it matters, I am imagining the relationship working by ChildA, ChildB, and ChildC all having the value Parent1 in their Parent field.) The children have a field called Score. Parent1 has a field called Total. I want Total to hold the sum of the children's Score field. I can do this by having a script run down a portal, total them up and stuff the result in Total. It seems, however, there should be an easier way, using a calculation or a summary field. Ideas anyone? Much Grass. (I am starting to mess around with FM7, so if a solution is easier in 7 let me know.)

I am having a little problem with a DB that has three tables relationships are established and makes use of portals. The three tables are Customer, Cars, Invoices The theory is that 1st create customer which also updates cars table with required information. 2nd raise invoice that automatically populates customer name and car details. This works fine except if the customer has more than one car then the first record for cars is brough over into the invoice layout. . I need some kind of popuplist/mechanism that would enable the user to make a choice dependant on selection of cars. Any help offered is appreciated, I have attached example of what …

I'm a little 7 brain twisted! I have 2 tables: People and Groups - I've placed a Groups portal (Cartesian) on the People layout. In groups I have GroupName, PeopleID and cPeopleID. PeopleID is a comma delimited list of group members and cPeopleID is a calc field that shows the person viewed in the parent record is a group member (shown by a check box). PeopleID populated normally via a script but I can't get cPeopleID to calculate as desired. I'm using PatternCount(?;?) and tried a variety of options with and without new TOs but just can't visualize it and am not getting lucky! Thanks in advance.

I have a Purchases DB containing 2 tables, the first table (Purchases) contains the order information and the second table (purchasesItems) contains the line items to that order via a relationship [PurchaseOrderNumber = PurchaseOrderNumber]. My Question: Is there a way show related line items from the current found set in the Purchases table. Example: If the current found set in the Purchases table displays say Purchase Order numbers: PO.0001 PO.0004 PO.0020 I would like to create a GoToRelated or Find script which would take me to the PurchasesItems table and display all the related line items from the current found set above.

I have created a portal in a "new widow" in order to select a related record in an original window. The portal list names in the table. By clicking on a name, I can get to that persons record in a layout of my choice. Every thing works fine but the action in the "new window" takes me to a layout and related record in the "new window" instead of the original window which is my objective. How do I modify the script in the "new window" in such a way as to get to the related record in the original window? I hope that's not too confusing.

I have a file called Product Catalog that contains about 3000 different products. This file is used by everyone in the company for reference, placing orders etc. Needless to say, this file is getting rather large. I have one user that needs to access about 21 different layouts and 10 scripts to print individual pricesheets. They are the only person that will use these layouts and scripts. I would like to take those scripts and layouts and move them to another file that only she will use, but will access the information in the Product Catalog. So I made 2 files out of the 1 Product Catalog. Problems: 1. I am not sure what is the best way to form a relationship betwee…

I want to connect directly to the address book of my Mac. Is this possible (I want to make a relationship with it, instead of csv)

I have two databases, where I am trying to relate records by a number. The problem is, that in the first database I do not have the exact number, but an interval (given by values in two fields). Example: Database one, record A: Interval 1-24 (field A=1, field B=24). Database two, record B: Value=17 - how do I relate these records? In plain words, I need database two to look up values if it itself has a value in an interval specified in database one. The problem is my intervals can be as big as 1700, else I could populate a field with the values in between the interval.

I have a bunch of attributes that I want to be able to view selectively in a portal. The attributes are set via a global select field which is then tied to self relationship. No problems here, quite easy with FM7 now...no need to do multi Rel calcs. However each attribute also has a wild card or "All" option. I've accomplished this by using the replace function to set another field to be all if the global select is all or set it to the attribute for that record. This works fine, but its ugly brute force and for this application is ok as the quantity of records is quite small. But if I want to do this on a large structure the replace funtion would be qu…

I have a beautiful relataional db in FM Pro 6 -- one file, Inventory, has a layout, Order Worksheet, in list view of the items that I sell (including the manufacturers I am using) to set up pending orders, etc. In a seperate file, manufDB, (with just one l ayout) I have each manufacturer entered into its own record. How can I create a button or pop-up menu/list or portal in Order Worksheet that will trigger a script to selectively sort by an individual manufacturer (and keep current as I add new records in manufDB for the manufacturers)? I know how to create several scripts that will sort by each of the various manufacturers, but it is neither self-upda…

Okay I have a file which is basically a relationship between 2 contact IDs. So the file format is basically ID1 ID2 NoteConnection What I want to do is to be able to list: ID1 - ID_A - ID_D - ID_F - ID_E - ID_B - ID_C - ID_D All the contacts that are related to that contact and the contact related to that contact and so on and so on up to 6th one. How is this possible? Any help on this would be appreciated Thanks

Hi all... it's been some number of years since I dived into FileMaker, but now we need to add a feature to our suite of related databases developed with v3, now running in v5. OK, so we're a design office and keep all time cards in FMP. We track project time by phase. What I want to do is see the accumulated phase totals, by project, in a related data file where I can crunch the numbers and perform some analysis against budgeted time. What I can't figure out is how to display the subtotal of hours per phase per project into the related file? I suppose the first step is to write the correct code to display these subtotals correctly in the parent file, but th…

Hi. I have a file of records with account numbers. Whenever a new record is created, it is assigned an account number. What I want to do is, whenever a new record is created, to have the data from a field of the previous record with that account number to be automatically entered. So for example, if I make a new record with account number 101, I would like the "price" field to auto enter the price data from the previous record with account #101. Anybody know how to do this? I guess it would involve sorting the records by account number, and grabbing the data from the corresponding field of the previous record. I'm thinking this requires some sort of self join, b…

I am so confused right now i'm about ready to toss my laptop out the window. My problem is relationships, specifically how to change one once I have one established. I have 5 databases. 1 main one and 4 children. The way I have the relationship set up now it is to name. However after reading more and learning more about FileMaker Pro I discovered the auto entry serial number option. I beleive this would be a better relationship to have as it will always be unique. The problem is the database is already in use and already has over 104 files in it. I've searched the forums and have found a few other tips on how to do this but I was unable to make any of …

Post deleted by mcbain1212

I have two files, one with all the data and a second with just table occurences referencing the first file. I have a found set of records in the first file. How do I find that set of records in the second file? I can't use the 'Find Related Records' script step because in the "Show record using layout" popup, I don't get the layouts that are in the second file.

Hi! I have a problem with calculating the price for a reservation. In my main table I have two date fields: day of arrival and day of departure In the price table I have a lot of post with different prices that changes over the seasons. Now, I want to calculate the total price in my main table. Let

I am sure i have done this before - but somewhere my mind has lost a cog (or two) I am using Fm7 and I a need a relationship to show training seminars for paricular months. My parent file contains contains two global fields (gMonth and gYear) My child file contains seminar details (Title, start date, end date, details etc.) I need a portal to show all seminars for that particular month so that as the use changes the parent's month and year so the portal changes to reflect the seminars active during that month/year. As an example say, gmonth = 09 (September) and gYear = 2004 There are obviously a number of choices - Seminars that - start in August and e…

Anyone know if it's possible to merge portal data into a summary field in a master table? I.e. if I have a portal showing a field called NAME and it shows 3 related records (Moe, Larry, Curly) can I create a local field in the master table that can merge the portal output into: Moe Larry Curly I've always wanted to have portal data accessible in a single summary field but I have a feeling I'm up the creek on this request. Thanks all.

I'm posting this again, since I'm not able to work it out so far. I've got three files - the first (EVENTS) is the main data entry file. The second file ALLNAMES is just a repository for the names (a one to many line items file - and won't be accessed by user) and the third file STAFFNAMES (to be accessed infrequently) is a selection of staff-only names. Data to be entered in EVENTS is name and type of person (ie: staff or other). When entering data in EVENTS the entry of names creates (via a relationship EVENTS ID - ALLNAMES ID) individual records in the ALLNAMES file for each name. That's all fine so far no probs. When I enter a person type in EVENTS as …

With large database, I'm wondering what technique is faster : perform a search or go related record with a relationship ? Chris

Hi Al I wonder if you can help me. I have a portal that is displaying line itme from a related table. eg. Qty....Item....Unit Price....Total Price 1......Dog.....20.00.........20.00 1......Dog.....20.00.........20.00 1......Cat.....10.00.........10.00 1......Cat.....10.00.........10.00 1......Cat.....10.00.........10.00 What i want to do is display the following instead Qty....Item....Unit Price....Total Price 2......Dog.....20.00.........40.00 3......Cat.....10.00.........30.00 I'm completely lost. If I make a report with subtotals in the related table, it's fione, but I cannot get the summary to appear on my main form (with fields …

My portal works fine in displaying data. A problem occurs when the user repeatedly clicks on the down arrow in the scroll bar at the right, after the portal has scrolled to the last entry. After the last entry is reached during the scroll, repeated clicks on the down arrow causes the last field to display garbage and this display is shown in each preceding field above with every repeated click on the down arrow. In another portal, instead of garbage the fields above display the contents of the last field in the porta, again, in the progressively upward fashion. The fields return to their original values when clicking onto them. This occures in portals that allow new …

I am a brand new user to filemaker, so try to bear with me . I'm using "Purchase Orders" as a template. I have one field named "Product Number" and one named "product description" I made these into pop up lists and have them linked to a table that i made in excel. So here's my question: How do i select a product number from my list and have it bring up the corresponding product description in the next field. I tried to set up a relationship thinking this would do the trick but had no such luck. Thanks

Hi! Running 5.5 To avoid many-many relation i

As I understood FM7 multi-criteria relationships to work (perhaps incorrectly now, it seems) I thought you could create a relationship with say, three elements and continue to narrow down your relationship output as you defined each element. More specific example.... Simple IT problem tracking database. Key fields of: Status (Open or Closed) Assigned To (An IT support person's user ID) Request Type (Project or Break/Fix) Globals defined in the same table to anchor the relationship. Status Anchor Assigned To Anchor Request Type Anchor Relationship built on all three in an EQUALS eval statement each. Then, I was hoping that by set…

I am trying to create a portal based on a relationship that looks at two fields in a file, one of which is generated from another existing self-relationship. However I can't, as apparently the field that already depends on the slef relationship cannot be indexed. Is there any way around this problem? In the attached example, the relationship I want to create would look at "UniqueProjBud" and "Unique key". But UniqueProjBud is already a result of a self-relation (that returns 1 or 0 depending on the uniqueness of the record) and thus the new relationship then will not work. Grateful for any tips jw testdb.fp5.zip

No excuses for my ignorance... unless you count simply being brain dead. (Under the advice of one of our local heroes) I have 3 related DBs. DB1 = Attendees, DB2 = Customized Data (DB1 is disposable data after an event is concluded), and DB3 = Meetings. DB3 contains various meetings (most, not all) attendees in DB1 are assigned to attend with a field for Topic, Speaker, Location, Group and Time. Each attendee in DB1 is assigned a Group Code (there are more attendees in a group than each meeting has room for so each meeting re-occurs). Currently I have a relationship set up to display each attendee's meeting schedule in DB1 using a Portal with the relationship b…

Hello, hope you can help me. I have one DB where I have customer info (Table A). In another table I have a satisfaction questionaire (Table :. This last file can have several records for each customer and the difference is the date it was applyied and results. I have some sumary fields that grade the questions. I have a layout in Table A where I would like to show the summary fields from table B by poping up the different dates that the questionaire was applyied and when selected to show the results (fields from table : I have created a relationship customerid=:customerid Can you help?. I know this maybe a basic question. I have read a lot through the …

In my invoicing database, I have it set so if you enter an account number it uses that as a look-up to fill out the company name and address fields on that invoice. What I'd like to do in addition to this is set it up so that if someone enters a company name, it looks up the account number for them and thus fills out the address, etc. I'm actually not limited to doing it this way, I basically want to be able to automatically fill out company info whether I enter an account number or company name. Using 2 different relationships on the same file doesn't work -- it always says this lookup includes the field in its definition. Yet one lookup uses account number and the o…