Relationships

If I have a field for my product number, for example, P001(text), and I like to set it as my primary key. Is it necessary to create another primary id (number) for internal use? KC

Dear All, If I want to add a new record in the portal in Master and have a new record appear in related table b, how can I achieve it... Do I need to write a script for it? Many thanks for helping. I stuck for hours... Add Record.fp7

I am newbie, When I check the box in product layout, how can it also show Sidemark picture from another table. Many thanks. KC checkbox.fp7

I don't understand relationships like this. Instead of just the name of the related database there is a field, a backslash, then the name of the database, another backslash followed by another field. Here is a picture of an example. How are these relationships created? Why are they used? Can anyone point me to an in depth reference that covers them?

HI, I have the following relationship which works perfectly when a teacher logs in and they only see the students in their form group. However, student sometimes move between form groups for different reasons so if there is an end date populated for the record in the final student tutor group table (holds complete history) then I don't want that record picked up and the tutor able to see the student. I know this can be done in a relationship but I'm struggling to figure out how to do it. I've created a global field in my global table called ActiveTutee (which I intend to leave blank) but filemaker won't let me link this to the current table occurrence and I'm not sure h…

We have Permits which will expire. We want to create a Notice record as the time draws near ... when 30 days away, then again when 15 days away, and again at 5 days away (as indicated by the Actions table which is established by the client). There are many approaches and I keep going round and round about how best to handle it (which usually indicates I know my ideas are not the best solution). An existing Notices process is in place and we'd rather not touch it so my plan is to add an Actions table and then use filtered portal to display either the existing process (left-side portal) or new Actions (right-side portal) as in the attached fp7 file. My question is in …

Hello, I have a table holding records of members of staff. Another table holding details about Events ("CPD") that the staff can attend. I have a linking table "CPD Attendance" where I hold records of which members of staff have attended which events. Screenshot attached. I want a portal of all records from the "CPD Attendance" table where the 'CPD Type' (a field in the CPD table) is of a certain type. I know I could create a layout sorted by sub-summary type to see this, but I'd really like this data in a portal for ease-of use for the end-user. What's the best way to achieve this please? Very grateful... Ben

I hope I can make myself understand correctly (I’m not English native speaker). I’m trying to find out the correct ERD for this. I have properties (Table), owners (Table) ONE property can have MANY owners (no problem with that), but after this, there are many things that can change and I don’t know how to face them. - Let’s say: A boat has two owners. Each one have 50% of the ownership. First owner sells his part to a third one. Now I need some kind of historical because owner 1 has been so from XX/XX/2015 to today and owner 3 from today until ... - Another example: John owns a house (100%), since XX/XX/XXXX until today that he sell a 50% to his wife Mary. I ha…

Hi all, I am attempting to analyse retail pricing of products in multiple locations. I have the following tables: Products Set Price Sites Published Groups (My other tables, GenericProd and Products_Analysis are to assist with grouping similar products and calculating profitability) Each Product can have multiple Prices. Each site is associated with just one group, but can be also advertise in a publication, which may be a different price (and therefore should override) the Group price. Can't get my head around how to relate my tables. My clumsy first attempt is attached. If the published price is less than the Group Price, for example, I can't work out if my …

I am adding a new table to a system. The purpose of the new table is to be a personalized gateway into this rather large existing system. I am trying to define a relationship where the current projects for the user are presented in a portal on the gateway layout. So far so good, and it is working… with a catch. The problem is the user might be assigned to one of three different functions in the existing file the data is pulled from. The user's name could appear in one of three discrete fields, in any of thousands of records. I have it working based on a relationship to one of these three fields. But I can only find a way to have an "AND" relationship to the other…

Hello, I'm having trouble with the initial set up of my relationship. For the most part it is a simple 3 table (one to many...many to one) design with a sort of "line-item" table in the middle. There are two sticking points for me:1) I would like to set up the relationships based on the "status" of the line items and 2) the portal should only show records that belong to the user logged in. I am attaching a picture to show the simple table relationship between my current tables: user, charges, patients. I have set up the privilege set so that the user can only see his/her charges when logged in, so I think I have sticking point 2 taken care of without requiring the r…

I have a database that stores the contents of—I'll just say it—Magic decks in such a way as each card-number-deck combination is its own record (this allows for other features not available in a single text field for each deck). My ultimate goal would be to have a means of comparing a given deck to all other decks for the number of cards in common, but this will always be less than 100%. I have a Decks table, a Comments table, and a Contents table. The relevant fields are these, where "relevant" is interpreted loosely: Decks::DeckName, Decks::VersionNumber, Comments::DeckName, Comments::VersionNumber, Contents::DeckName, Contents::VersionNumber, Contents::CardName, Cont…

Hi folks. So the problem is not really that complex I reckon but I'm missing some piece of a puzzle here and can't make an elegant solution. Consider a situation where we have a specific part number that's been superseded by another part number which in turn may or may not have been superseded by yet another part number. Sometimes there's no supersession and sometimes there are 5 or any other number. A relationship is simple: BasePN = NewerPN, all in a single self-relating table. The problem is that I need to cycle through that list not in a script but rather get an instant result inside a field. What I need to get is a list, starting from some arbitrary point (record)…

Hi - I've been trying a number of approaches to get a calculated value list using ExecuteSQL code. I initially tried putting the SQL code directly into the Value List definition but that didn't work. Right now, I've done this: I created a UnusedClaims field in a Visit table, whose value is calculated by this: ExecuteSQL(" Select \"_k1_xSAClaim_ID\" FROM \"xSA Claim Status:xSAClaim\" WHERE \"_k1_xSAClaim_ID\" NOT IN ( Select \"_k2_ClaimID\" FROM \"xSA Claim Status:VisitClaims\" WHERE \"_k2_VisitID\" = ? ) " ;""; "" ; Visits::${Visit ID} ) I then put a Claim TO in and related its primariy ID variable to the UnusedClaims fiel…

Hi - I'm trying to do something that seems like it should be doable, but I can't quite figure out how to do it - I've thought about using relationships and SQL, but neither quite gets me there. I have 4 tables: Visits, VisitClaims, Claims, Account. The relationship between Visits and Claims is many to many, thus the join. When I'm standing on the Claims table , I can easily see all the visits that are assigned to that Claim - simple. If I'm standing on Account, I can see all the visits that have been assigned to claims using that account What I'm trying to do is stand on Claims, and find Visits that have NOT been assigned to claims using the same account as the cu…

Not sure where to put this question: When I view the tables tab in Manage Database, I can not figure how to make all the table occurrences show so I can not see all of them listed and I need to see them. I can only extend my screen so wide. Even if I drag every column to very narrow except for "occurrences in graph", my screen is still too narrow to show them all and it is frustrating me to the utmost max. Why is there no bottom scroll on that view so I can scroll right? Or how can I make it so I can see them all. Thank you for helping me.

Hi everyone. I’m having some trouble designing a database and I thought I’d ask for a little help. We need track the people coming to our facility and which services they use while here. Here are the details on what we need to do: Every day we need to generate a list of the guests currently in the facility and a check list for each of them that shows which of the offered services were done or refused by the guest. (Guests will be in the facility from one day to several weeks. ) I thought that maybe three tables would work: Guest Day Join_Guest_Day The Guest table that would have people’s ID and other information, plus their arrival date and a departed …

I don't have a good grasp on the relationships but kinda see how they work from what I've been playing with on my filemaker. I've used the lookup function to get by before but it's not working for what I need this time. I'm trying to make my work order system show us if there is a report# associated with it. Is there anyway i could get some help on how this works. This report layout was created after most of the other layouts so I don't think something has the relationship working correctly.

Hi all In our tickets layout we select the associated user via a 'personnel' value list, this references the 'personnel' table, via a relationship that excludes records marked as 'old'. Just yesterday, users have reported that this value list doesn't work, when the associated field is clicked, nothing pops up. I have seen it, however when I am logged in it does not occur for some reason. When I remove the 'via related records' option in the value list, it works for everyone just fine - but includes the old personnel. What I have noticed though is that in the portal where personnel are specified (within the settings layout) (this is via a relationship that just shows a…

Hi There, Can somebody please assist me on extending the projects starter solution to implement on the existing Projects, Sub projects with Sub Tasks - This is for the reason that some Projects are very broad and there is need to break them down into sub projects which also will constitute sub tasks. Will appreciate all guidance and thoughts shared, Many Thanx, Miss A!

Hello Forum. Having a little trouble with a Cartesian join (or self-join), pic attached. I have a layout based on a table Event_attendees. This table is a join between Events & People. I am trying to put a portal on the Event_attendees layout that will show the other events that attendee has attended. It's not entirely necessary, but would come in handy. At this point, I'd rather learn how to make it work, just for my own knowledge. When I put this portal on the People table it works fine. To put it on the Event_attendees layout I need a second TO (Event_attendees_other-event). I tried (obviously not correctly) a second TO of Event_attendees, and a second TO o…

Greetings! Sorry if my title is vague, I am not sure exactly how to describe this problem. I have taken a look in the Forums, and haven't found this question before. (If I did, please point me there.) And of course I've looked through the documentation in FM extensively. I am a botanical garden curator, and this database focuses on two tables: accessions of plants (groups of plants received at one time, with multiple individuals), and on locations of plants (individual plants of one accession, but planted in different areas). For each individual Location (of a single Accession) I would like to assign it a unique qualifier for that accession. These qualifiers are b…

Hello, Can somebody please advice me on what will happen to my records in a related table if i delete the existing relationship in order to insert a table in between? Or what and how possible a solution if i would like to add another table as a join? Many Thanks, Miss A!

Hello to all When i duplicate (one or more times) a table on the "Relationships Graph", what is the diferences between this tables? They are only one table? There is any webpage where i can lear more about this issue? Thanks to all for your help

I'm beginning a design where I want to provide flexibility when it comes to a person's name. Most names have first, middle, and last but others have other types of names including title, alternate, nickname, and others. I want to store each individual name component in a simple child table with an index UUID field referencing the parent record, name label, and value. I would use a portal to allow the entry of various types of name types. I would like to store the results of the name entries in the child table in different formats in the parent record. One with first, middle, last and another with last, first, middle. There may be others as well. What's the approach I s…

I am implementing an email feature into my solution. I have created a separate file based on the Personal Mail example file that comes with the MailIt plugin from Dacons. My question is must the relationships to the tables be made in the source file or to the table occurrences brought over into the UI file, or both?

Hello I have several db which we use to edit films Unfortunately each clip has more than one names according to the various people who looked at it hence the original clip name field is no longer available I wonder if it is possible to create relationship smething like a "conditional relationship" (if this is the proper way to call it) I have a Main DB with every various fields. Each field has the various names assigned to the original clip If I want to relate DB2 to Main DB is there a way so I can conditionally create the relationship In other words DB2 will relate to The MAIN DB if one of its fields matches a selected field in the MAIN DB Something like this IF…

Hello, This is my first time using this forum so please excuse any mistakes.. I have setup a standard pos system with DB for invoicing a customer, inventory and purchasing inventory from Maker. The DB is setup in such was so that I have a layout for Invoicing the customer and within the invoice are the line items for each product that I carry in the inventory. The Inventory is defined with barcode, description, prices and most importantly stock. The purchase order is setup the same way as such so the i have layout with purchase order sheet, then within are the line item with barcodes, description etc... Now, the problem that I am facing is that i am using calculations…

Hi everyone! I am a surgeon trying to create a FM solution to analyse some of the cases I have been involved in. The main table I have set up is: Patients and one patient can have many "Referrals"; "Consultations"; "Admissions" and "Operations" - I have set up one to many relationships and this all seems to work fine. I am working on the relationships between "Operations" and "Surgeons"; "Procedures"; "Antibiotics" and "Complications". I believe these should be many to many relationships; i.e. one operation can have many surgeons and one surgeon can do many operations. Thus I have set up a join table containing the foreign keys for Operation and Surgeon. I want to be …

This question is related to handling records in a transactional way by using portals to create related records. In my first use case, though, the transactional part isn't really important; but there is a second use case where I would like to use the same structure (scripts and schema) where transactions would be important. The general structure currently: I have 4 tables related in a straight chain, ala an invoices-lineitems-sub_lineitems fashion. But the 1st table isn't relevant in this issue I don't believe. I attached a picture of the relationships. The important ones in this case, I believe are the Revisions, LineItems, SubLineItems tables. The relationships are…

Greets, all. I want to create a join table for a many-to-many relationship (teachers - students), but it's between two files. Does it make a difference which file has the join table or no? On the surface, I don't think it does but me being intellectually-challenged, I could be wrong. Thanks in advance for your replies!

Hello , i have a invoice solution with an inventory table so when i create an invoice the inventory updates .... I want to know how to cancel an invoice and reverse the inventory but at the same i want to keep all the data of the cancelled invoice including the invoice number.

I have 24 layouts that are populated with fields from multiple tables present in the relationship graph shown in the image below. (I realize this is not uncommon.) Is it possible to determine which layouts use each table? In other words, is it possible to identify which of the 24 layouts have fields from the table "Contacts", which of the 24 layouts have fields from the table "Contacts 3", which of the 24 layouts have fields from the table "Customers", etc.? Thanks! Joe

Hi, I'm working on a knowledge base / help system. It contains articles. I'd like to create links from one article to another as related articles, which show up in a portal. Right now I'm doing this with a Join table that contains a pair of ArticleNumbers, one for each of the related articles. This relates to a second Article table occurence. So I can easily link Article 2 to Article 3, which is set up as an entry in the Join table. The problem is this: I'd like the inverse links to automatically get set up, i.e. if article 2 links to article 3, article 3 should also link to article 2. Right now, they are just one-directional. Do I have to have pairs of li…

Hi there, In FM13, I have a layout with about 10000 records, i would like to create another layout/duplicate and display only specific records of the 10000 records already existing on the main/old layout, i have a drop down field that defines the category of the records(e.g, Employee, Ex Employee, Accommodation, Embassy, Car Hire etc) - is this possible, by relationship definition or any other way, how?

I'm using replace field contents to update an "ID" field by getting it from a related table (related by other identifiers) that has the new ID. That works fine, except that it replaces the field with a blank in cases where the new table has no matching record. Is there a way to avoid that? thanks

I have a database of classical music with 32,000 records where the field "Composer(s)" is entered in the format "composer_1 / composer_2 / composer_3" etc. I want each name to be clickable, so if I click on "composer_2" then opens a window showing the CDs we have with this composer. When the "Composers (s)" holds up to 15 composers (composer_1 / composer_2 / composer_3 etc.) 15 columns in the primary database will upset and confuse and I suppose that I need to create an independent database of composers? In the composer's database I want to list all the CDs in the primary database that contains music by that composer. How do I do it? Please advice me with an easily und…

I'm having a bit of a brain fart, and I'm missing the obvious (or at least in my own mind) - can somebody throw me a lifeline? This is a project management solution and I'm trying to relate checklists into the project. In P1 table (main project), there is an area for checklists. each checklist may have 20-200 points for checking.. Checklists may include a simple yes/no check, data entry, multiple choice, etc.. (these main fields are the same across all checklists, which is why I categorized all checklists to a single table.. The value names for each line of these checklists (there are numerous checklists related by project type) are in a different table [PC1]. My plan…

Hello all, I am new to this forum and also to filemaker. I have seen my colleagues use it and I believe it is what I need for my purpose and have now installed FM Pro 14 to get going. I would like to organise my vast classical music recording library in my house that consists of many recordings saved on various types of media, including Lps, cassettes, Reel to Reel tapes (alot of them), DVD, VHS, BluRay and also on my computer hard drives. I am in need of some assistance and some ideas how to go about building a database with relationships as to facilitate searching for a specific recording or an artist etc. I would like to be able to display my collection by say tape…

Hello everyone, I'm sort of a newbie to filemaker, and I made this relationship diagram that I am wondering if it is actually constructed correctly. I have a number of items that are priced differently in different outlets. I am trying to "audit" the daily transactions for each item, with daily sales totals and inventories. Any advice would be appreciated.

Hi Guys i was wondering if anybody could help me as I'm going crazy. My database is as enclosed picture and has been working brilliantly until i wanted to add the 009 and 030 layouts. each customer has different staff (BS Input) some then also do IMI but not all . I have a portal on the BS layout to populate the IMI. There is two parts too the IMI that they can do. 1 is 009 and the other is 030 In BS testing i have a portal to 009 and another for 030 I also have a button to a layout on each that i have to input more data for the 009 . when i press this button to go to related fields i get a different customer? I'm sure this was working but now its not. Thanks

Has anyone developed a (free or paid commercial) template for a very basic calendar system, written entirely in 100% native FileMaker lingo, also without making use of the web viewer? By very basic, I mean there is no need for drag and drop, consists of just a few tables, has one or two views, and has a scripts no longer than about a page length. I am aware that there are fully-featured commercial calendaring solutions, written in web languages - I am not interested in any of those. Should the calendar solution consist of any of the following, I would not be interested in it at all: HTML, PHP, JavaScript, JAVA, CSS, jQuery, SOAP, Python, perl, .net, Flash, or scripts and…

Hi FM Forum friends, I am attempting to create a way that I can assign a "Main point of contact" to a record via a related table. What I would like to do is have a field that I can place the Contact ID number into, this would then change the main point of contact fields (ex, firstname, lastname etc.) to match the contact in the related database in the organization table. There is a list of over 10,000 contacts, and I need a way to easily see who the main point of contact is for this organization and still have the ability to view additional contacts through a portal. My relationship is: Org - Org_Contacts - Contacts If you think of a better solution, or if I am thinki…

I need help clarifying the best approach for this solution. It is an older solution being completely rewritten so the structure will be different in build after this next release we're working on but for this one, I still want the best solution possible. None of the existing process can be changed since this setup is used substantially throughout the existing solution. Situation: There are many Contacts involved in a Project. Only one person can hold each of these Contact Types (applicant, owner, tenant). There are several of these but I've kept it to three for simplicity. The current structure has been replicated in the attached fp7 file for clarity. Future version wil…

I have a straight forward Order Entry application. I want to summarize products sold by Code Size and Type, Customer data is entered into a Invoice header with child Line Items. I established a relationship to the global table called Dash with the Products table. I can summarize the product Code, Size and Type (in the product table) in 3 different portals, by quantity and amount (in Line Items) just fine . The number of records in the Code, Size and Type table will vary. But I cannot figure out how to filter the data for a preselected yearly interval, the selected year field is a global field in the Dash global table, but I can't figure out how to get to the Invoice head…

Hi, I apologize if this is the wrong part of the forum for this question. I have searched both this forum and the filemaker.com forums for a few days. I am looking mainly for advice. I am working on a filemaker solution to track two different types of inventory as it is moved around the building to different projects. One type has individual barcodes/serial numbers for each asset. For instance we may have two of the same product, say a mac book pro, but they would each be individually barcoded giving them a unique identifying number. The second type are items that it would not be cost effective to individually barcode, so they have a barcode for the type of item it i…

Hi forum, had a question about a database design (not sure if this is the right spot). I would like to create a separate database for my business to manage vehicles. I have 3 tables: Vehicles, Service, Vendors, connected in the usual way. Vehicles Table: Has all the basic info about the vehicles. Service Table: Will be connected to vehicles to record service performed. Vendors Table: Info on the vendors/suppliers who provide the service. The 2 parts that has me stumped. Part I I want to be able, for each vehicle, to put in a preventive maintenance schedule. Some examples of what I need to track are: 1. Inspections due-regular vehicles are once a year, comm…

Hi, Â This might be a simple question but here goes. I've got a staff layout based ont he attached relationship. I'd like to only show those staff members with a status of Active. I've got a global variable that is set to "Active" and is linked to status field in my staff table. The values in my staff table are only Active or Leaver. However, my layout is showing all staff, leavers included. Is there a way I can only show active staff based on the attached relationship? Do I need to do something additional to this? Â TIA.

Hi everyone, Â More and more I find myself moving less-used fields, or fields which group together, off into a secondary table based upon a 1:1 relationship (one-to-one). Â Narrow tables load faster than wide tables particularly over WAN and, let's face it, it is the future and desktop is losing ground. Â Everyone wants mobile and particularly, the ability to switch perspectives and tools (whether iPhone, iPad, desktop or web direct) because we are constantly on the move. Â I don't know about you, but MY ONLY provider is the slowest in the United States (6 mbps download/.76 upload) ... so low that others laugh when they hear it and they usually send flowers. Â :-/ …

Hi Guys,  I know there are many posts about conditional value lists, but I can quite seem to find one related to how I am setting mine up - maybe its because it doesnt make sense to do it this way.  See my screenshot attached, but basically I have 3 related tables - I am using this file as a basic demonstration of what I want to do:  Class > Category > Sub Category  I want to have drop downs that filter just like a normal conditional value list. Where I would like it to change from the standard setup though, is I would like the values for Class, Category, and Sub Category to be in their own tables. The reason for this is I already had these tables an…

Hi All, I'm looking to design a database for allocating passengers to a particular seat. The concept I was thinking is having a field for each seat (4A, 4B, 4C etc) You enter the passengers details then enter the allocated seat with their name. This then copies their name across to that seat field, colors the seat in, and adds their name as the tooltip. Is this the best way to go about it? Cheers

I have two related tables, "LOG" and "LOG_ITEMS". I want to create a new table occurrence called "create_LOG_ITEMS" so when I enter a value in a field from the "LOG" layout it creates a new record and then it immediately breaks the relationship after the new record is created. Can someone please explain the technique for doing this?

Does everybody always use the base table name in the TO? Example, I have a base table called events and on some TO's I would use training in the title of the TO. Does anyone have any reason why I shouldn't?? I appreciate any response...thank you!

Hi there, for each record in table "Beleg" I need to create 1-5 records in table "Buchung". How can I easily create records in another table (within a script) while remaining in the first table? Switching layouts is way too slow when you have lots of records. An answer for this first question would be cool. However, this begs for another question: What about the 1-5 records that need to be created would delete themselves when the user makes changes to the records in the first table? Ideally, a commit record in "Beleg" should automagically insert, correct or delete the corresponding records in "Buchung". BTW, the table "Buchung" will not be edited/changed in any way by t…

This will be lenghty, please bear with me! In 'portalizing' my scheme, I want to use a single layout where I can do most everything. It contains multiple portals (inside a tab set with more stuff that's not related to the problem I think). The first portal (shops) points to the currently focused table with an x relationship, so all shops are shown. From here, I want to be able to display contacts (customers), orders, and line items. It is supposed to work like this: Click a shop in the shop portal to select it - matching contacts are shown in the next portal. Click a contact in that portal - matching orders are shown in the next portal. Click an order in that portal…

I am trying to build a database to track my college students' enrolments, evaluations, attendance, etc. I hope I will clear in what I am trying to ask: I have the following tables (there are others, but these will suffice for my question). Sessions Course Offerings (with foreign key field id_Sessions) Sections ( with foreign key field id_Offerings) Enrolments ( with foreign key field id_Sections) I started building the structure as such: Session ==> Offerings ==> Sections ==> Enrolments. Please see the attached PDF. But I was wondering if I should have a foreign key in the Enrolments table for each of the parent tables? In other words: Enrolments (wi…

Hi Everyone!  I have a situation where I think I need a many-to-many relationship, but I'm struggling so much with how to make the user interface for the joins, that I'm beginning to doubt that many-to-many is really the answer in my case. Here's the scenario:  We have tools that make more than one part number. We have part numbers that need more than one tool to make.  So, I set up three tables: partnums ---< join >--- tools  partnums::kp_partnum = join::kf_partnum tools:kp_tool = join::kf_tool  So far, so good. My problem is when I want to create an interface for a person to make the join relationships. We will have a list of approxima…

I'm not sure the best way to go about this. I have a Jobs table and a Jobs Pictures table, the latter of which can contain N number of pictures for a given job record. I would like to have a flag on the Job Pictures table that indicates that one picture in particular is the "Front of House" picture. Then, on the Jobs table, for report purposes, I would like to be able to have a calculated field or way to reference that particular field. What is the best way to go about this? Jobs::JOB ID <----- Job Pictures::JOB ID Jobs::Front Of House Pic ----> Job Pictures::Picture (where Front of House flag = true) Ideas? Thanks, Ryan

Hello, I have a database with the information of every student in the school linked and everyone has a unique ID provided by the school. Now, I have access to their school schedules from an excel file. Each student has 8 periods. What is the best way to create a relationship so I can have some sort of portal on the layout with the student information to show their schedule sorted from period 1 - 8. What I currently did was have 8 excel files each with the student ID and each excel file had only one period. So I imported the period 1 and period 2 but without matching, i just kept adding new records. Right there would be 8 records in the "Schedule" table all with th…

Hi! I have an Hours Sheets table, and hour sheets are created per month. I have Absences table which has absences specified by start and end date. and I have a Calendar days table, which is related with Absences with start date and end date. Now, When I create an absence spanning over two or more months, (e.g 26th March to 5th April) I want to get related calendar days for the particular month in hour sheet. (i.e if I create an absence from 26th March to 5th April I want to have 3 days (26th, 27th and 28th) in hours sheet of February month and 5 days (1st, 2nd, 3rd, 4th and 5th) in hours sheet of March. I am not able to find a way to separate out the days based on …

As I put together a solution that I intend to offer to several different clients, I'd planned to have a table called SETTINGS, populated by global fields, where they could adjust some variables. For example, I can write the calculation that opens event registration X days before the event, but they can decide how many days that needs to be. Another example: I can write in email functionality that they can use if they want to provide their own SMTP server data. I'm just learning, though, that global information can end up purged on a hosted file. And while there might be workarounds for that, it means that this isn't a rock-solid solution, so I'm in search of another o…

Hello, In the address section of my database, when you select either Canada or USA it provides a list of relevant ‘Provinces’ or ‘States’ in the Province/State field. Currently the provinces/states are written in full but I would like that once a selection is made, the field automatically inputs the abbreviation - ex. someone selects ‘Saskatchewan’ and it auto-enters/replaces it to ‘(SK)’ on commitment. I managed somewhat to achieve this using the substitute function but only for one entry, I was not able to enter multiple substitutions – ex. Substitute (Province ; “Saskatchewan” ; “(SK)”). Is there a way to enter multiple substitutions as a conditional value? Is …

Hi all I have an issue which I'm hoping is easy to resolve. I have 2 tables, Plots & Stages. There are many Stage records related to the plots table. I would like to display the related stage records in a horizontal row under a Stage header. In my Plot record I have a unique id and I was hoping to use a repeating calculation field, get(calculationrepetitionnumber) with 50 repeats which gives me a repeating field with values 1 through 50. In my Stage table I have the plot id, a stage value ( a value from 1 to 50 ) and a data field. I was hoping to join the tables by both the plot Id and then the repeating field to the stage value and then display the …

Hello! I have a Project record and it pulls Product records via a portal. My Project has a Manufacturer drop down field... I am finding that the Products table should need this field more so, that way I can have multiple products with multiple manufacturers... What would be a good way to transition all existing manufacturer data into a Product field? I would like to also be able to continue using my Project drop down to cascade the manufacturer into products, but also be able to override individual items if needed. Thanks

I Want to merge two related files into one. As far as I know I will have to create a new table in file #1 with all the field names in file #2, then import the data from #2 int #1. This is tedious as there are 50 fields in #2. Is there an easier way to do this?

When creating a record in a child table on a layout based on the child table ( which has to inherit the PK of the parent table) I figured out that there's a simple way. I've used FM for ages and never saw this. On the child layout create a field Child::FK which contains the foreign key of the child table. Make that field get its values from a value list defined as Parent::PK which shows the Parent PK values plus a second field. Say Name. Uncheck show only related etc. now when a value is selected the Child::FK is successfully populated with a proper match. Doh! An easy fix. I can't believe I never saw this before. Or maybe I did but forgot. Seems like a basic technique. …

This may not be the right forum... I have a layout that is VERY slow to load. After reading through several threads, I know why. My approach in this layout is not good. My solution contains tables: Students, Transactions, TransactionLineItems ... A layout showing records from the Students table contains a portal showing records from TransactionLineItems (assets currently checked out.) Now the "bad" part. This portal has several unstored calculation fields - example: The due date for checking out an asset is based on (4) other calc fields: 1- checks the allotted time for an asset (2 days, 1 week etc) and assigns a due date 2- based on above date it checks to…

I have a database that contains information on groups of objects that, in the physical world, are discrete entities. I want to create a table that can extrapolate and hold the discrete elements, assign unique values to each, then randomize them. For my purposes, it is simple enough to think of the table in question as having only two fields: ItemCount and ItemName. To use the common example of fruit, I could have the following: 4 Orange 3 Apple 2 Banana 1 Pomegranate From this I want to have a table hold 10 records, using ItemName and SequenceNumber: Orange Orange Orange Orange Apple Apple Apple Banana Banana Pomegranate From there, I should be a…

Hi all, I have a file with investments. A portal beneath based on a normal serial number shows all children records for that investment in a table called transactions. The main file also specifies, among other things, the currency and the status (open or closed). To make it a little less simple, but hopefully more useful, i want to display results of the investments over a particular period. My tracking filter is another TO of the main file where I want to be able to disinguish among records by currency and status. In the main file, I have a global key that is something like key (text) = currency|GetAsText (status) (one for each currency) The relations…

I'm trying to add a portal to my PEOPLE layout that shows a Person's MENTOR. The Mentors exist in the PEOPLE table just as those they mentor do. So when I go to the PEOPLE layout, I want to see the person's information and a portal that contains their mentor. Also, when a Mentor's record is viewed, I need to see all the people they Mentor in the portal. Would this require 2 portals? Creating the layout and portal are not the issue. I need to know how to properly create the relationship for this scenario. It seems straight forward that a Self-Join is needed but I'm not sure how to setup the key fields and the join itself to make this work. Thanks !

Hi! I have a problem... again... There are 4 tables: Calendar (CAL), DutyWish (DW), DutyNotWanted (DNW) and personnel (PER). If a person wishes to work on a certain day, the record is added to DW-table. And same if he definitely don't want to work to DNW. I wanted to show ALL those names in CAL-table that have NO wishes either in DW or DNW -tables (i.e. no records in either tables). But how can this be done with relationships? The CAL-table have dates and they can connect to dates in DW and DNW -tables. PER -table have names. Thanks in advance!

Hi, I have (at the moment) a very straightforward database. Two tables, with one table containing data for 'Time' worked on various jobs by employees. Also in this table is 'Department', 'Client Name' etc. The other table is 'Jobs'. I have created a third table called 'System' which has a single record with an ID of '1' which links to a Foreign Key in the Time TO which in turn links via a Foreign Key of 'Job Number' to the Job TO ... System>Time>Jobs. I have a layout based upon 'System' which has a portal on it that is linked to the Job TO. I then added global fields to the 'System' table and created multiple relationships between 'System' and 'Time'…

Hi! All, I have two tables: Global Fields gMCOID gProviderTypeID gCategoryID gStartTime gEndTime gElapsedTime Services ServicesID MCOID ProviderTypeID CategoryID RequiredTimeLow RequiredTimeHigh I have a Layout to create a record in a third table (ProgressNotes). On this Layout I would like my users to enter a start and end time and have FM pull in data from the Services table based on the elapsed time and how it falls between the values in the Services Table - RequiredTimeLow and RequiredTimeHigh. The two tables are matched (=) with the first three fields in each table. I have tried to do this. gMCO…

I've been racking my brain trying to figure out how to filter my value list. Here's what I have Tables: Flights Airports FlightLegs (A join table for flights and airports) Terminals (there are many terminals at each airport) On my flights layout I have a portal so that I can add however many flight legs as I need for each flight. I've created two TableOccurances of airports "AirportsDepart" and "AirportsArrive" so that I can have a departure airport and an arrival airport for each leg. Once the user selects the airports I want the list of terminals show only the terminals at each respective airport. My relationship setup: …

I'm facing an issue caused by wrongfully exporting the initial database (which is not available anymore). 3 tables: properties, organisations (companies) and contacts.  Properties::REF_tenant = Companies::REF. Companies::__pkCompaniesID = Companies_contacts::__fkCompaniesID (join table between Companies and Contacts) companies_contacts::__fkContactsID = Contacts::__pkContactsID  The goal is that 1 property can be rented by multiple companies and/or can have multiple contacts. For now the multiple companies are the same companies, but all with another contact.  Therefore Companies::REF should be a primary key (and unique). Unfortunately it isn't. If multi…

I am not sure where this fits, but I need structure help. I have a solution that allows checkout of assets, but recently I was told that it would be nice to have items that could be in kits (but also potentially checked out individually.) My current relationships are: Tables: Transactions, TransactionLineItems, Assets, Students Transactions:TransID::TransactionLineItems:LITransID Assets:AssetNumber::TransactionLineItems:LIAssetNumber This is the issue andwhere I need help...I created a new table called Kits and created a relationship with Assets: Kits:KitsID::Assets:AssetsKitID This works fine as I can add and subtract items from kits. At the same time, …

Hi to all, I am total newbie on this ...so sorry for the stupid question. In my strugle to understand filemaker i have the following scenario. I have a table with the following records and fields. ............field1........field2 rec1....1/1/2014.....3 rec2....1/1/2014.....5 rec3....2/2/2014.....12 I nedd to create another table that i keep the grouped sums of the above table and updated every time a new record adds like.. rec1....1/1/2014...8 rec2....2/2/2014..12 similar the sql command SELECT field1,sum(field2) FROM table GROUP BY field1 How can i do this?

I haven't had experience with complex relational databases most have been simple & didn't require much planning so I could really use some help. I'm trying to create a db to track training for a group. The project needs, goals or whatever they are called are as follows:  The group has many members that need to be tracked for position certification The group has 19 different positions available Each member can become certified for more than 1 position Each position has a task book and a Job Action Sheet (JAS) associated with it Each member will have their task book(s) evaluated several times before becoming certified Each member also receives 1 JAS for each …

Not sure if this is the correct forum for this ... I am currently developing a file to record class enrolments and producing the resulting student invoices for a small unit providing various classes. Students can undertake one or more classes and enrol for a term of these classes. Students can make a one-time payment to settle their invoice or can make part payments during the term. Many students re-enrol for another term of their classes following the end of the current one. There is a table for each of the following: Students, Classes, Invoices, Payments, ClassesLinkedToInvoices with the structure below Students ---< Invoices ---< Link >--- Classes …

My name is Alberto and I'm new to the forums so excuse me if I miss important information on my question. I will try to be as accurate as possible. Ok here it goes I have two tables joined via a relationship. Table A (Records) and Table B (Forms). The relationship is setup via a global field in Table A (t.record.id) = field in Table B (t.form.name). I have a button that performs a script that sets Field (t.record.id) in Table A (Records) to the form name I want related info from. Then the script goes to a layout based on Table A (Records). At this point is where I can't figure out why if I place a field in the layout for t.record.id I get the value set by the scrip…

I need a little help getting my relationship filtering to work. I've searched through many of the examples and issues others have had, but for some reason i did not find an answer to my problem. I've tried join tables and multipe one to many tables and while I can get some of the results i'm looking for, i know my end result is incorrect and there is a better approach to the data. To start with; I am given a table of transactions that contact a salesperson and customer (around 200,000 records). My data table will have a record looking like this: salesperson_id ; customer_id ; sales amount ; date Bob1 ; Widget100 ; 100.00 ; 1/2/2015 Bob2 ; Widget100 ; 100.00 ; 1/2/20…

I apologize for my bad English. I've written a few times on this forum but I could not get a result. The staff of the company I work from home, I plan to move the database to work from home business. Where the job description as follows: TABLE: CUSTOMERS BUSES DRIVERS ROUTES INVOICES FATURA_LINES PAYMENTS Buses with the specific route, picked up from the office staff to the office in the evening customers are left home away from home in the morning. Pricing is done by time. Every day time are the same. Each customer is different, there are different routes and prices. Each customer is done with 1-year contract. Billing is done monthly bill payments cut Getti…

Everyone Hi. A relationship problem I'm having for her help I need. My scenario is as follows. Car running buses var.Bu tools to companies and factories Personnel taşıyorlar.Otobüslerin distinct, separate unit prices var.Sabah and Evening fee in the form of fees alıyorlar.Bunların accounts and To keep the accounts of the companies they do business and a table how do I need to build relations. Nuri 1.Tablo Example: Tour 2.Table: Charter Buses 3.Table: Services of the Company or Factory

Hey Guys, Having a bit of a nightmare trying to get my head around filtered value lists, and I'm guessing it's a relationship issue. I've got two tables: Item - Fields (id, name, linked_category) Category (cat_id, name, filtername) When creating new items, in the items table, I'd like to be able to have a category dropdown that I can filter (over 300 possible categories). I was hoping to do this by using filtername, whereby the dropdown list of categories that I can choose for an item shows only values where filtername=value. Whats the best way to go about achieving this ? Thanks James

Hi Board! Here's a problem I have, that i just don't understand from a Schema point of view, on a basic level. I've tried to do this sort of thing before and failed, but now i really need to do it, so I've got to learn. Can you help me understand? What sort of relationship is this, that I need? How do i do it? I have four tables: A 'Months' table - summarises data by the month for display on an automatic Dashboard A 'Shipping' table - logs products going into boxes being packed. An 'Orders' table - order headers An 'Orders_Item' table - the order lines for above. The last three are giving me the aches.... (I'm only listing fields that are concerned …

Hello, could someone help me with this situation please? I would like to change the color of the switch port according to the selected VLAN. I've tried in different ways but couldn't find a solution. Any help is welcome. hp_switch.zip

Using a multi-criteria relationship I want to accomplish a conditional value list. pk_ID (not equal) fk_ID cTotalAmount (greater) gZero but as cTotalAmount is a calculation field it doesn’t work properly. I’ve tried with a calculated numeric field (=cTotalAmount), but this doesn’t updates automatically as a calculated field. Any suggestion?

Hi I am developing a database for measurements of samples. Each sample has one or more measurements. Each measurement has one or more work items When a sample arrives I add one or more measurements to the sample. For each measurement I copy work items to a table "Work items in a sample" and for this record I log start time and also time when the work item in sample is finished and the result for the work item. I have a list of work items in sample which are not finished and every time each work items in sample is finished I update the list. So if a measurement has 5 work items in sample and 2 are finished I only see 3 in the list. I wan…

I read a post here recently that referenced the Lookup function, which I wasn't familiar with. Did some digging, found this -- http://www.filemaker.com/help/html/relational.11.21.html#1028978 However, can those who are smarter than I (i.e., most people) provide information as to the usage scenarios for this function? I'm missing, basically, why you would use this, as opposed to properly defining a relationship between tables in the first place.

Hello, Â I've got a problem which is at least related to relationships (!), but I'm not really sure this is the appropriate forum. Fairly sure it's a noob problem though, but I have no idea what to start googling for. Â I have set up relationships between 3 tables (see attachment), and display data from all of them in a layout (2nd attachment), using the BOM table as 'basis'. The dropdowns are populated with valuelists from the names of projects and companies, respectively. Â What I would like to happen when I chose a new name for either company or project, is that the relationship updates - that the BOM is now related to the newly chosen company/project in stead…

I have "Customer" table linked to "Address" by CustID. No Problem there. Each customer can have multiple addresses with multiple services. I'm trying to figure out a way to link the "Services" table to the "address" table but here is the issue. I want all the available "services" to show up in a portal in the "address" layout. I've linked them by using a "address_lineitem" table, since the price is different for each "customer" Is there a way to have that portal of all available services dynamically update if we add a new service or change the name of a service in the "services" table? Do I have to script it or is there an easier way? I feel like the…

I have a parent and child table I hide the child field until the parent field is populated via 'hide object when' which all works. but.... if the child field is not on a portal and I click in that field, the field disappears. ( note: file attached, make sure there is no related child record, to see it in action ) hiddenRelatedFieldWithoutPortal.zip

Hello been battling this one for a while. i have the standard starter invoice solution and have copied the stock transactions table from the "inventory solution" so i can get the stock number to decrease when i create an invoice. this did work. see product details layout for the portal. the problem is that i have to create a dummy invoice add the product then i can update the product stock in the portal. otherwise i get This operation cannot be performed because one or more required related records are not available and cannot be created. i have a problem with the relationship i am guessing the only way i can update the value in the portal is if i ad…

It has been a long time since I've done any Filemaker development so am asking for some assistance. I have a "People" table I have an "Accounts" table The people in the people table can be assigned many accounts Accounts can have multiple people affiliated with them I decided I needed a join table so I created a "JOIN_People_Accounts" table Ultimately when a person is placing an order, I will need a pop up list of accounts from which they can choose. Each person can have a different set of accounts. I think I understand I need a portal to do this, however I'm not sure how to get the data to populate the portal. People table has "Unique_ID_#" field …

Hi I'm new to FM. I'm trying to create a database showing a person who owns multiple companies. I've created 1 table for Owner and 1 table for company. In the Owner Form, I need it to show the owner details and the list of companies he/she owns. I also need a way to add new company name to the Owner Form that can reflect on the Company Form. On the Company Form, i need it to show the Owner and Shareholders of the company. Thanks in advance.

Table : Vendor (parent key) Table: Estimate_line Item (Foreign keys) Table: Estimate (parent key) I need to see my available estimate line items including the vendor name who provided the line item on a master estimate layout. My portal for vendor and estimate line items works fine. I can see all line items related to a particular vendor in a portal on my vendor layout. Now I want to show a portal in Estimate of all my estimate line items and include the vendor who is related to them based on the Estimate line item topic. So all "toy cars" estimates would be together and can then see all the different vendors who provided estimates for "toy cars" So in my p…

Hi, I have a parent table containing ID and some other basic fields. There are two child tables feeding into this - each with their own set of fields. Parent: __pkParentID Child: __pkChildID, _flParentID,...(other fields) I have many records that I wish to import from an excel doc into a child table. I can do this, but can't work out how to auto populate the parent table. Any hints/tips etc? Thanks in advance.

Hi All, I am fairly new to FM, but am an experienced developer of .net applications and have used access in the past also. I am building a form that will need some business logic. Basically, upon update of field A or field B I need to create logic that checks that both field A and B are populated. If they are both populated I will then need to take both those values and execute a query (using A and B values in the where clause) to return a value. That value I will then display on my form. Ok so in C#, F#, VBA or SQL I could knock that up in a matter of minutes; but In FM The solution is not readily apparent. I can see a trigger point to execute script but want…

Hello, I'm a relative newbie to databases and Filemaker - so please excuse me if there is a really easy answer to this that I am not aware of. So... here is my scenario: Purpose of database: to link together people, organisations, evidence (i.e., pieces of information relating to people, organisations, or the relationships between people-org, people-people, org-org). I won't bring all the tables into this discussion as one the problem has been solved in one area, it is solved everywhere. So, taking this particular instance: My tables are: People: __pkPersonID, title, firstName, surname, AKA, fullName, DOB, DOD, information, notes Organisation:…

Hi, my name is Mathieu. I'm relatively new to Filemaker and and new to the forum. Merry christmas to everybody and thank you all in advance for your support. I have a layout for estimates with a portal showing LineItems items: Clients------>Estimates---->LineItems<-----Products I use price lookups in lineitems to make sure that if a product price change, it doesn't change in already completed estimates. My relationships are set up so that an estimate doesn't make a change in inventory. Now, the problem is, I'd like to, when a estimate is accepted, check a radio button in the estimate layout and automatically copy my lineitems items, at the …