Calculation Engine (Define Fields)

I am trying to design a word game using a word of nine characters. The player will see only those characters jumbled into a 3 x 3 square and the character in the centre spot is classified as mandatory. The player must create words of more than three characters using the characters of the word but including the mandatory character. Assuming the word is FORMATTING and the designated character is the letter T, what algorithm or script will check that:- a: only the letters from the word have green used b: the letters have only been used once. and c: the designated letter is included in the word Is these conditions are not met, then a message to be disp…

I really was not sure where to ask this question. I hope I am in the right spot. I am inventoring technology equipment at a very large school. I am building a database so we can track all of our equipment. Here is the problem that I have. I have many rooms that have many computers. All of the same kind and they are indentified by a Service Tag# and an Asset tag#. How would I implement that in a database. Right now I am doing, Computer#1, Computer#2, Computer#3 etc... up to 30 computers per room. Is there a better way to put it in a table? Maybe a value list? Please advise. Thank you.

Hello, I am not sure if a calculation is the best way to do this but any help would be greatly appreciated. Basically I have 2 tables Enquiries and Jobs with a field on each called dept. Dept is a drop down list which on Enquiries has values QA, QE, QP and on Jobs has values A, E, P basically without the Q. I have a script that when an enquiry becomes a job copies various fields (using set variable) opens a new job record and sets the fields with the data from enquiries. What I want to do is copy the dept but drop the Q when entered on the Jobs record. I have tried using a calculation on the Dept field on Jobs - if(Dept = "QA";"A") but it doesn't work. …

I am building a timesheet database and have hit a glitch in the calculation fields.  We have 2 types of hours - flexitime and time in lieu which is time worked before 6 am and after 8pm.  All time between 6 am and 8pm is called flexitime.  I have adapted the timesheet calculations from the Time Cards DB in FM Starter Solutions but have added some code so that any start time < 6:00 AM or > 8:00 PM is excluded from the daily flexitime.  Here's one of the calculations for Hours That Day:   If(IsEmpty(StartTime4) = 0 and IsEmpty(EndTime4) = 0;  Round( Hour( If(EndTime4 > "8:00 PM"; "8:00 PM"; EndTime4) ) -  Hour( If(StartTime4  < "6:00 AM"; "6…

I may be asking this in the wrong place since it pertains to the printed page instead of a GUI layout. Please forgive me if I have. Â My question has to do with text parsing. I would like to have to individual city names to print without the comma or state name or airport code. Â In other words, show only the words to the left of the comma. Please see the attached image. Â The formula I am using to get the text block is; Â List ( TextStyleAdd (Â Origin_1&" to "& Destination_1 & " -- " &Left ( DayName ( Date_1 ); 3) & ", " &Left ( MonthName ( Date_1 ); 3) & " " & Day ( Date_1 ); Bold); Airline_1 & If (not IsEmpty ( Flight…

I am trying to write a script that will tour a binary tree. Each node in the tree has 2 children, Left and Right, a classic binary tree. I wish to visit every node in the tree. I wish to tour the tree in a breadth first manner. I'd like to tour all level 2 nodes, then all level 3 nodes, etc. I'm having a hard time getting my head around how to approach this script. It looks to me like it will involve recursion. Can anyone share their ideas on binary tree scripting?

Hello, Any help on this would be greatly appreciated as I am well out of my comfort zone. Basically I have 2 related tables, Jobs and Transport orders. On the T.Os table I have a cost field and a cost with markup field. I also have a field to select whether the job is chargeable or not with radio buttons yes/no. On the jobs table I have a portal showing related T.Os What I am trying to do is under the portal on the jobs table have a field that totals the chargeable and a field that totals non chargeable T.Os. I have tried doing this with a calculation field with and IF statement that says if the chargeable field = Yes, sum charges but it isn't working. …

Hi Guys, I've been working with FileMaker for about a month now and am busy hacking away at my first project. Up to this point I've managed to find solutions to all of my problems on these forums and others but I've now come up against a problem that has me stumped. What I would like to do is to setup a calculation field that compares the current values of a field to the previous record value for that field in a sorted portal. I have a portal that is showing related records. My current portal has 4 related records in it sorted by date and looks like the following: Primary Key: Review Date: Value: Changed? (Has value changed since previous review -…

Hi, I have a portal with various fields, the two in question however to explain my problem and without listing them all are called: - Previous Years Remaining Remaining When I go to a new record on the portal I want to get the value from the field 'Remaining' (from the previous record) and put it into the field 'Previous Years Remaining' in my new record. This I have managed to achieve by setting the 'Previous Years Remaining' field type to calculation and using the following function. GetNthRecord (Beneficiary Allocations For Accounts::Remaining_c ; Get(RecordNumber)-1 ) Why when the value or data in the 'Remaining' field is updated or changes however,…

Hello,  I am new to FMP and learning the script steps. What I am looking to do is go through each field in one of my tables and if it equals 1 then I want to add a record to a seperate table and copy over some data. Most of these fields are checkboxes, so making them equal 1 was easy enough.  To give more detail - I have a customer table. Each customer has a room(table), and each room has a ScopeSheet(another table. The scope sheet is basically a form with a bunch of checkbox fields. These scope forms are filled out, one for each room. All these tables are related through relationships with keys.  What I want to do essentially, is go through each field in…

My Serial format is as follows YY-XXX, year last two digits, and a 001-999 serial. I was wrongly under the impression that the following code, that checks to see what year it is, and if it’s the same it increments, if it’s new it starts anew, was correct: If ( Left ( GetNthRecord(_pkProject_Serial_Number; Get(RecordNumber) -1) ; 2 ) = Right ( Get(CurrentDate) ; 2) ; SerialIncrement(GetNthRecord(_pkProject_Serial_Number; Get(RecordNumber) -1);1); Right ( Get(CurrentDate) ; 2) & "001") Sadly is not, after a find request, or in between records. I tried to change it as follows but now I don’t get ANY serials. If ( Left ( GetNthRecord(_pkProject_Seria…

Greets, all:  I just need some guidance how to do this. (See attached. Sorry, but I'm not allowed to post the solution file due to privacy issues...though I did change the student's name in the attached.)  It's a simple sub-summary report as you can see, but what I want to do is extract only the courses and dates for a student that have a count greater than three; in the attached, that would mean that only PE- Individual/Team Sports--with its four dates--would appear for this student.  I tried going the script route with a looping function that would find and constrain data but it got to be incredibly unwieldy, so I'm thinking there must be a better way.  …

Hello, If anyone can give me any advice on this it would be greatly appreciated. We have a quite large excel spreadsheet for working out job prices and I was wondering if I should replicate this in filemaker. I can easily reproduce the spreadsheet and calculations but we have multiple lines which I thought I could do using repeating fields. Is this a good idea or will it slow the database down? I thought about using portals but there could be 20 lines of calculations per job and I thought it might get a bit complicated. It would be great to hear anyones thoughts. Thanks James

Hello I am currently making a database for cancer research in the hospital I work at and I am trying to set a calculation field that will calculate certain regimens of chemotherapy and with summarize them as their generic name. For example, I have a field called "regimen" that is a text box containing the value list: Dex Bortezomib CTX Cisplatin Adriamycin Etoposide Vincristine For the summary field I want it to calculate whats in the regimen field and give me a chemotherapy line For example, Bortezomib + Dex =Velcade Bortezomib + dex + CTX = CyBorD Cisplatin = DHAP etc I know I will have to include the paragraph symbol and I tried using t…

Howdy, all: I have dozens of buttons scattered throughout my solution and it's a pain to have to manually change each one's tooltip text when a change arises. For example, there's a COPY button on each of the 12 layouts available to the user but if I need to change the tooltip text for one I need to change them all one at a time. Welcome to Tedious City. Is there a way to create a (global) calc so all the COPY buttons show the same tooltip text when a mouse cursor hovers over them? It's tricky because I know I can create a calc using Get (ActiveLayoutObjectName) for a _field_, but for a _button_? No such luck as far as I can tell. I tried creating a global tex…

If you use the re-lookup field contents function over a found set of records, is there any way to determine which records were changed as a result of the look-up, or do they all get changed? I'm working with a legacy database which runs such a relookup process. The found sets are quite large and at the moment, I have to process the whole found set afterwards as I don't know which have changed. I'd like to reduce the found set to just those that have been changed. Brian

Hi all, I am printing a directory from a membership list. No problem getting it laid out, sorted and grouped by country, state and last name but I want it to print like this: Country State Member 1 data Member 2 data etc…. and the problem I have is that the state field uses state abbreviations not full state names. I am trying to come up with a way to have the full state name display in the printed directory but still have the abbreviations in the address block of the data entry layout. Appreciate any creative ideas, Thanks!

In the attached file, given this current data model, I would like to know the best way to calculate the following two results: From the context of "experiment" 1. the amount of the most recent sample of type "BA-A" for each experiment 2. the amount of the most recent sample of any type starting with "BS" for each experiment Additionally, I realize there may be a better way to model this data.. any suggestions on that front? (note: I am helping a beginner with this and he may not be inclined to change his data model, so being able to calculate 1 & 2 above would be a big help.. but, if I could give him suggestions regarding modeling what he's tr…

Hi everyone. i have a calculation that gives me the employee name and phone number. Name_displayFull.c & "-" & Employee_phone_cell i am putting this calculation on an iPhone layout what i want to happen is when the user clicks on the calculation it calls the employee. here is the script which works for the other phone fields but not on this calculation field Dial Phone Filter ( EMP__Employee.tog::Employee_phone_cell ; "1234567890,+" ) anyone know how to get just the phone number from the calculation field. Thanks

Hi guys, I have a problem I'm struggling with: I have a table named Manufacturings (parent key field = Manufacturings::manID) which has a field named Manufacturings::man_prev, which displays the unique other manID (entered manually) with whom the Manufacturing is linked to for warehouse reasons. For example: Table: Manufacturings field1 = manID (manuf_012) - parent key field2 = man_prev (manuf_010) - entered manually field3 = warehouse_qty (1200) I would like to create on the table Manufacturings (e.g. manID = manuf_012) a field which shows the warehouse_qty of manuf_010, i.e. field 4 = linked_qty I know I could make a long script along…

Hi Daniele, I'm trying to edit your Custom Function below which i found on the forum (http://fmforums.com/forum/topic/39221-unique-serial-numers), I want to remove the "-" between the serial no. How can I do this? Author: Daniele Raybaudi Format: MonthlySerial ( AEfield ; digit ) Result: text parameters: AEfield: text - the text field wich will hold the serial number; it must be setted as auto-enter, always evaluate digit: number - the number of digit (#) that must increment by one and restart from one every new month This custom function can be used for: 1) Autoenter a serial number that increments monthly 2) the format of the serial number is esse…

Hello all! I need a substitution calc which will change an HTML tag that has quotes in it and can't figure out how to make it happen. I need to change this: <p style="text-align: center;"> to this: @body_f1:<*C> Thank you!

how can i increment a timestamp by say 5 min to use in a formula. I.e Get ( CurrentTimeStamp ) < Creation_Timestamp + 5minutes confused on which function to use. thanks jW

Hi I have an auto entry calculation field with value from the field "serial" which has serial increment Right(Year(Get (CurrentDate));2) & "-" & Right("0"&Month(Get (CurrentDate));2) & Right(("000"&serial);4) The output of which is: yy-mm#### ; 13-110435 .... 13-110436 ......... How can I make calculation to reset the value "serial" in order to start from 1 at the beginning of every new month Thanks

I have two fields - "toggle", which is a global, and "duration". Toggle has a value of either 1 or 0, entered by a script. Duration is a calculation field using the Case function to determine it's contents (result is text): Case( toggle = 0 ; enddate - startdate & " days" ; toggle = 1 ; [insert week and day calculation here] ) thing is, if the duration result in days is less than 7, then weeks aren't displayed (basically 0 or 1 would return the same result). How do I turn days into week and days?

I have been tasked with segmenting our customer database alphabetically. I have certain territories which require any customer organization whose name starts with A-M go to one employee and N-Z to a second one. If the Organization = Bluefish Industry and it matches any character within the list "ABCDEFGHIJKLM" I would like it to return 0 or 1. I would also like to be able to handle stripping out any articles in the event the corporation name is "The Bluefish Industry" I was looking at using PatternCount (Left(company_name;1); "ABCDEFGHIJKLM") however I don't believe I have it formatted correctly. Any ideas?

Hi Everyone, I have a database table containing around 700 fileds and half of them are auto enter calculations. Every time i create a new record it takes a long time to create, even more the first time. I just need the record to be created, rest calculations can be done afterwards through scripting. I cannot remove the calculations. Have tried creating the record in seperate table(with the required data only) and then importing those data into my main table, but still, it takes a while. So i was thinking of a way if i can supress the calculations while the new record is created. Is there any or other way of achieving that? In desperate need of this perf…

Hello I have a list layout with a title header. I have a calc field Get (Current Date) in the header. I use a script button to run a script, one part of which says, Set Field, calc field, Get (Current Date). While the date changes the printed version does not show the current date. Has anyone an idea of this issue. What am I doing wrong? Thanks Matthew

I'd like to summarize all records with the contents "A" in a certain field - those are all of my active accounts and I'd also like to summarize all the records with "N" in that same field - these are all the non-active accounts. What would be a good calculation for this? I was thinking something like if - said field = "A" then summarize Thanks

I see that when you use Left(return separated list; 10) it gives you 10 of a count of every character in the list. So if you had 10 list items all 20 characters long in the list you just get back 10 characters of the 1st one only. How can you use Left with a list and make it shorten every Item in the list?

Hi! I have a field containing a file name for example: af10-2689_001 I am trying to remove the prepending "af" and everything after the underscore just leaving the xx-xxxx in the middle. This is what I have so far, but it is not working. I am getting an error message that "List usage is not allowed in this calculation." Not sure what I doing incorrectly. GetValue (Substitute (file name ; ["af" ; ""] ; ["_" ; ""] ; ¶ ) ; 1 ) Any suggestion would be most appreciated. Thanks!

I have the result in a return separated list (it came from ExecuteSQL) something like this: DOC000000095 LNK000000080 LNK000000081 I also have another list DOC000000081 DOC000000088 DOC000000089 DOC000000090 DOC000000091 DOC000000093 DOC000000094 DOC000000095 DOC000000096 What is a way FileMaker can say if any of the values in List 1 exit List 2 evaluate as true? I was trying to use FilterValues ( ) Inside of PatternCount () but it went sour and I forgot the whole plan.

Hi I am looking to extract information from a web page when I try to get a translation of a word. I can get filemaker to open the web page but I just want to translation as in this example http://translate.reference.com/translate?query=misinformation&src=en&dst=de I want only "n. falsch Auskunft, falscher Bericht" I guess this is called web scraping or something close to that. I am trying to automate the extraction of that part that translated. Any help is so appreciated Thanks

Is it possible via ExecuteSQL to use a variable like this? Let ( @sort = INFORMATION::exsqlsort ExecuteSQL ( " SELECT "name" FROM "TOPICLIST" WHERE "_fk_infoparent_id" = ? ORDER BY "name" @sort" ; ""; ""; INFORMATION::_kp_information_id ) ) Then I would go to a field named INFORMATION::exsqlsort setup as radio button with the choices ASC and DESC.

For money exchange, I have two fields "DOLLAR" and "EURO". Is there a way that entering the amount in one of the fields would give the result in the other, and vice versa. Or do we need to have two separate procedures.

Hi, For linguistic purposes, I would like to have the output of sentences as follows: Input ; John and Mary are students Output : j o h n, a n d, m a r y, a r e, s t u d e n t s Then I would like to have the output in a (portal or something else) linked to the record of the sentence, for searching purposes. Thanks

Hello I have the need to mix up a large amount of records. I want them to be sorted so they are completely random. No order at all. Is there an easy way to do this? Thank you.

In layout A, I can readily display the desired field value (date) from a related table (using a filtered portal where I filter to display related records where a different field (Course Type) in table B = a particular string. The relationships involves multiple fields and is also sorted to display the desired date. That works fine. I need to use that date (obtained from table B ) in layout A/table A in other calculations, so I need to place that date in a field in Table A. I'd rather not use a script for this. -> Is it possible to write a calculation that would result in the same value (date in this case) that I see in the filtered portal? (I can do t…

I have made a quiz file. I want the user to be able to add a duplicate of a question they are having a hard time answering. They get that question displayed more often. But if they are getting the answer correctly after a while they can delete the extra duplicates. Some way I need the original questions to remain in the database so at least one copy of the question remains safe. The user can then duplicate or delete a record but not the original. Is this somehow possible or scriptable ? Thanks

Here's a simple to do list table with the usual items like "_pk_Task_ID" "Task Description" as a text field, due date, start date, Priority, etc. I have 50 separate tasks in the table. I wish to have 10 of them prioritized from 1 (most important to get done) to 10 least important. That means there are 40 tasks without a priority assigned to them. And I've struggled to figure out exactly how to do this - focusing primarily on this being a calculation result in the priority field on a layout. (Hope I said that right...) It could be that today task_ID 31 is denoted as priority 1, task_ID_22 is denoted as priority 2 etc. I may decide later that task_ID_31 gets shifted …

Forgive the ugliness of this but that is the way it is. The first shot is a calc which works, taken from FMStandards or something. When I want to comment it out so it does not run, it scrambles itself. I copied exactly the three definition values into the three code blocks below. You will see that all I did was put /* at beginning and */ at end. It not only wraps with quotes but removes all my spacing and carriage return which makes reading the calc more difficult. Why does it do this please? Calc working fine Let( [ which = "backward" ; ~CommonHistory = $$BrowserNav.CommonHistory ; ~Window = Get( WindowName ) ; $$BrowserNav.Windows = $$BrowserNav.Windows …

Hi I have a field called "remove" and this field contains words that I want removed exactly from another field called "Sentence" I would like to have a calculation to remove the contents from the field "Remove" from the text field "Sentence" Thanks

I have a field with names and addresses: Bob Smith 123 Main St Miami, FL I just want the first line "Bob Smith". Can someone tell me the text parse code? Any help would be greatly appreciated... Thanks! Left( Receipts::cBilling Copy, Position( Receipts::cBilling Copy, ¶ , 0, 1)) Nevermind, figured it out...

Hi there. I am trying to setup the invoice part of my patient database, so that I can choose a different billing address/payer than the patient. Sometimes it is an insurance company, the patients company, etc that should be billed. In the Invoice table I have created a "PayerChoice" field as a two options radiobutton: Patient and Company In the Invoice table I have also created a "PayerName" field with an auto-enter calculation: If (Invoice::PayerChoice = "Patient" ; Patient::Name ; Patient::Company ) I have inserted the two fields on a tab (Invoices) in my patient layout. I also have a button in my Invoice tab named "create new invoice". Normally when I…

Dear experts, Â I've got a student table linked via a linkup table to 3 or more courses in a 3rd table (see screenshot). The linkup also contains the grades. Â I've been chewing on the following calculation: calculate an average of the 3 best course grades, e.g. discarding the worst if a student took 4 courses. So, I have to collect the grades via the student ID and then discard the worst if more than 3. Â I've already burned 2h hours on this since I'm not very familiar with FM. I hope somebody can help me out. Â All the best,

Hello all, I've FM 5.5 for Mac. I've a text field where I want to enter a validation by calculation where users are unable to enter any spaces, commas, dashes, periods or semicolons. What calculation may I use? Best,

I have a list of records in a table (CONDITIONS) shown on a list view. One of the fields is a checkbox when checked sets a value "Yes" to the field. I needed a calculation that shows the number of records flagged. What I tried: List of Records shown are based on a TO (Layout_Conditions) A field called "Flag" in each record is displayed as a checkbox to set a value "Yes" Created another TO based on CONDITIONS base table and named it "showCount_ofChecked" Created a global field gCheckedState and set it to "Yes" Created a self relationship between Layout_Conditions::gCheckedState and showCount_ofChecked::Flag Created a calculation field cCount_ofCheckedRecords…

Hi all FM fans, here is my scenario: We have a database of our employees. In each record is also birthday. What I would like to do is this: I would like to make one ?calculated? field which will show me all upcoming holidays in next 7 days. I found some solutions, where you can search for birthdays, but I prefer to have this as field which will ALLWAYS shows on right sight or our main database, so you can allways see who will have a birthdays in next 7 days. Is that possible? Thank you for your time.

I have a table name Company and Agent The Main Table is Company and the portal is Agent This is the Relationship Company ID (Auto Serial) == Agent CID (text) In the Main Layout (Company Detail) I have 3 Records In 1st Record Company Name : Company A Portal Record (Agent Table): Ana , Karen, Nina In 2nd Record Company Name : Company B Portal Record (Agent Table): Ren , Akin In 3rd Record Company Name : Company C Portal Record (Agent Table): Pao , Meri, Yuna , Key My Problem is How can i find a specific Name of agent inside the main layout (Company Detail) in portal For Example i want to find Yuna The Result should be Company Name : Company C Portal…

I have about 5,000 records. Those records are for about 300 different customers. Some have 1 record, some have 50. I'd like a way to see a list of customers, and how many records they have in their name. I would assume that would be fairly easy, but i'm not 100% sure how to do it. Example ABC Company (50 records) Bob Company (12 records) etc. Any advice? Thanks!

I have contact list with "date_of_birth" field and "date_of_death" field. I would like to have a field that sorts the contacts by the remaining days to their birthdays, excluding the persons who have died, in which case, the field should show the remaining days to their memorial or annual "death anniversary". Appreciate your help.

I want to find those records whose total is >1000. How to write this in script.I try smthng like these. Set Variable[$a;Value:1000] Enter find mode[] setfield[Table:Total;">"&$a] PerformFind[] But this is not working.

I am scripting an Import that I want to do without end user input. Im on the windows platform. The file I need to import, is always in the "W" drive and in a folder that is stored in a field named "id_session". The file is an xml file and is the only xml file in that folder. I do not know the name of the file but it's name also contains the value of the "id_Session" field. For example if "id_Session" contains 20000. The file would be in w:20000 The files name would be ????20000?.xml Can a filepathe expression be wriiten for this?

I am building a database of art pieces, and I have a field for Net Sale Price that is dependent on the field Type of Sale, which has three radio button choices from a value list (Private, Auction, Gallery). Depending on which of these three choices is made, Net Sale Price needs to pull figures from one of three fields: Private Sale Price, Winning Auction Bid, or Gallery Sale Price. It seems I need to use the Evaluate function in the Net Sale Price field calculation, but I need a little "kickstart" here... ;-)

I'm wondering if someone could suggest a calculation to convert a list of numbers into a list of two digit numbers. For example: "1¶3¶9¶14¶22" would be converted to.. "01¶03¶09¶14¶22" In other words the single digit numbers would have a "0" placed in front. Thanks!

Is it possible to create a text calculation (for use in an email) that will take the value from Field A "Serial_Number", and increment the number by another field "Qty" and create a line with a carriage return for each? Basically if Serial_Number = SN and Qty = 4 that I would get a text result of SN1 SN2 SN3 SN4 I don't have any related tables, and this is simply to get the Serial into an email. Thanks! d

Here is my concern. I have an address field with house number, street name and street type, like this: 100 Main St. Sometimes, when I import addresses, there are spaces at the beginning or at the end of the address, as well as more than one space between the street number and the street name. A field calculation seems required. What's the best calculation to have these extra spaces removed, yet leave ONE space between house number, street name and ST, Ave, Lane, etc. ? It would be great to also remove any "periods". A field calculation seems required for my auto enter and manual enter, to correct all. I'm thinking the same calculation could be used for my fiel…

I came across a bug today when using Set Field [] where you sum up the first rep of a repeating field without explicitly stating it is the first rep and the target being another repeating field regardless of if it is in the same field or not. In other words, if you do not explicitly state the field with rep 1 such as MyField[1] or GetRepetition (MyField; 1) but rather just refer to it as MyField it will ignore the value. However if you use it in a calculation field or the target field is non repeating field it works as I would expect. I made a test file and I tested it both with FM11 and 12. Whether you use the + sign or the Sum function the results are the sa…

I have the "date_of_birth" field and the "date_of_death" field I would like to calculate the current age, which is easy if the person is alive by using the Get CurrentDate function; however, if the person is dead, how should the value "date_of_death" field be used in the calculation to replace the CurrentDate value Thanks.

I have a list of line items with a sort field, that should number the record sequentially starting at 1. On record commit, I want to check that the sort numbers are sequential and if not run a script to number them so. I figure there might be a formula that could check the sum of the sort column against the number of records. i.e.: if there were 8 records, the sort sum should equal 36. Sort# Sum 01 01 02 03 03 06 04 10 05 15 06 21 07 28 08 36 09 45 10 55

I know this is easy but IM not particularly good at formulas. Can you please redo this calculation using the "Let Function" Sum(invoices|INVOICE_LINES::c_totalTax) *(taxRate/100) - Sum(invoices|INVOICE_LINES::c_totalTax) *(taxRate/100)*discValue/100 Thanks

Is there a way to get the value from the another field without any relationship on another table

Hi.  I have created a database with clients, treatment records and am now trying to set up an invoice part. The invoice part works fine with invoice numbers  in sequence (10001, 10002... etc) created by a number field with auto-enter serial.  My two  challenges is:  1)I need to set it up so that every user/account (at the time 3 or 4) has it´s own line of invoice numbers. Therapist 1 : 10001, 10002...etc Therapist 2: 30001, 30002...etc Therapist 3: 50001, 50002...etc  Each therapist is self-employed (I don´t know if it is the right word, but I guess you can understand). Therefore, due to tax reasons, they need their own separate sequence of invoice…

Hello I was trying to use a webviewer to add urls of view stock images I could collect for reference. When I enter the url in the url field, it is never centering the footage in the webviewer. Is there some way to calculate centering a particular portion of a web page so it can properly center the image for viewing. I included a sample file to show you. You will see the footage is not centered. The problem is obvious but I would like to fix it. Any help is appreciated.how to center image.fmp12.zip Thanks

Envision please a menu bar across the top of a layout, where the words will change depending upon a list that changes. The values must always be centered across the top of the marquee (no exceptions) and that is where I am failing (attached fp7). So taking 'theList', turn it into a repeating calculation which looks like the example (except have it adjust properly so it always centers on the layout). This single field will allow it to remain centered even when using anchors. If I could dream further, I would also like that, when User clicks a (character of a) word, all the reps around that word change color via conditional formatting (or even if only the text colori…

How to format time in 24 hour format e.g 10/10/2013 05:20:18 PM to 10/10/2013 17:20:18

Hi I am trying to make a simple invoice and catalog database for a friend. I get how to do it for the most part . I don't know how to script adding a new repetition when necessary. I have included a sample of the file but presently when I press the add button in the pricing database, it causes a lookup for the other information in the invoice layout but only under a new record. Could someone show me how to make it add to a new repetition when necessary using the add button but not making a NEW record. Thanks Very Much Dave Sherry Store.fmp12.zip

is there a way automatically adjust the height if the text is cut off in the address when i hover the mouse on it it should expand the height https://www.dropbox.com/s/p20rmnibufuzqtp/Resize%20height.fmp12

Ok. I have been reading that my file should have a null table with no relationships or fields. This makes sense to me to use as a landing pad for incoming users where it doesn't load other files or anything. I have single record tables where I use validation to stop creating a second record (gleaned from this site) where I auto-enter data of 1 then validation says it must be unique. I want the same similar protection but I want no records to be allowed so the above does not work. I realize I could stop record creation from other methods and I will by custom menus, no access to this layout etc but there is still potential that person might accidentally create a…

Hi. I am trying to create a calculation field that calculates a persons age from a line of digits (danish social security number). I have a "Cpr nr" text field where the data is entered as: ddmmyy-xxxx (120475-xxxx meaning the person is born April 12th 1975) I have created a calculation field, called "Age" where I am trying to show the persons age. I have looked through several solutions but can´t get it right. I know there is at least a problem with the year since it is only typed with the last to digits in "Cpr nr" Here is my calculation: Truncate (( Get ( CurrentDate ) - GetAsDate (Left( Cpr nr; 3 ; 2 ) & "/" & Left( Cpr nr ; 2 ) & "/" …

I have a field VALUE which I want to make singular only if it is already plural. Example: Drivers should be Driver NextOfKin should remain NextOfKin ...etc I do not understand why this does not work: Substitute ( Right ( "Drivers" ; 1 ) ; "s" ; "" ) Can someone explain to me why it does not work and what might work? Thank you. ps actually it would be the field instead of the text value "Drivers" but I should think it behaves the same for either since I can't get it to work for either.

I have run into a snag. I have two tables, one is called project and the other is called sales. The relationship between the two tables is a field called salesrep. The project table contains information on projects, specifically three important pieces of information that I need to sum up and display information in the sales table. For example. A Sales Rep will own a project and he/she will put a dollar value in the 'project_amount' field, identify which quarter the project will book in the 'billing_cycle' field and what the status is in the 'sales_projection' field. I can run manual reports all day long by searching criteria in these fields and performing a find and …

Hey guys, I have a layout called "Aircraft Hours" where I enter in details for an aircrafts flight. The field I am interested in this one is [date]. I have another layout called "Maintenance" where the maintenance for the aircraft show up. I wish to have this layout display the last time that aircraft flew. I am new to this kind of stuff. I am using from what I have read I need to use the 'lookup' function but I am guessing I need an "IF" function as well? I want the calculation to look up the last record entered on the "Aircraft Hours" layout and grab the date from the date field however I need the aircraft field to match on both layouts. The "Aircraft Ho…

Hello everyone, I am having a problem with counting the unique values in a sub-summary report. I have tried to use the Execute SQL calculation, but it didn't work, so I've completed the 'sum the reciprocal' instructions I've found in several forums. It works great until I do a find. Then the field is blank and won't recalculate again unless I make a change to the field definition in database and then change it back. The layout/report and all of the fields in it exist in the same table and the calculation fields are unstored. Detail: records are entered through a layout called: Message Encounter Form in which the users will take messages (each of which m…

I'm looking for a way to strip or filter out "accent marks" like this é The below code strips out the entire character. Filter( yourTextFieldHere ; "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890`~!@#$%^&*()_+=-[];',./{}|:"<>? " ) Wondering if there is an alternative way to strip out the accent mark but keep the character?

Hello I am trying to fashion an exchange rate solution to produce an exchange rate given a number of variables. An accounting record has an amount to convert to GBP £. The amount could be USD CAD or EUR. The exchange rates are monthly. I have created an EXCHANGE RATES table with the fields; RtYr, RtMn (rate month), CAD>GBP, USD>GBP, EUR>GBP I have an ACCOUNTS table with the fields Date, Currency and Amount. I want the field cGBP to calculate the required Exchange rate based on the Currency field and the Year/Month in the Date. So for example; when the ACCOUNTS Date is June 2012 and the ACCOUNTS Currency is USD ACCOUNTS cGBP is the EXCHANGE RATE USD&gt…

I'm a DIY.... unfortunately. I feel like I am missing a puzzle piece of knowledge and am hoping someone can point me in the right direction to research my answer. both tables are childs Table: Payroll time sheets- people log on and submit one record for the monthly time sheet. It has a total of 25 entry dates, to put in their time. Table: time off- people submit requests for leave, one record for each day off. What I want to do: when they fill out the timesheet, I want those fields to zero out if they have taken that day off. What I have done that does not work: If (Absent::date ≠ ${1date} ; 20; If (Absent::SS ≠ ss; 1; If (Abse…

Attached is a simple test database. There are 2 tables, TEST and TEST2. Each has input (text), record_number (auto entered serial). On the TEST table I have dup_check doing the following ExecuteSQL ( "SELECT record_number FROM TEST2 WHERE input = ?" ; "" ; "," ; TEST2::input) The goal is to have dup_check populate with the record numbers of any records from TEST2 that have the same value in the input field. It's working to the extent that if it finds a duplicate it will put a ? in the dup_check field. I can't figure out how to get it to return the actual record number itself from TEST2 table. Any help would be appreciated! Thank you.

Hi I have a bunch of premade scripts that format completely different styles of text in a merge field. I am asking how to make a calculation to execute a random script from the group. Something like Random (Script1, Script2, Script3) So it will play a random script each time.. Thanks for your help Dave

I'm using FP 11 with MacBook pro System 10.6.8. I have a database of about 3000 records with various fields, one being "date created". I want to add a field to my layout for "date updated" so that whenever I make a change to a record, this field shows the date of that last change. This ought to be pretty simple but I dont immediately see a way to create such a field in my layout. thanks

Hi All, I am struggling to create a calculation that lets me forecast income over variable time scales with variable interest rates. There are a number of contracts with terms ranging from 24 to 60 months which have built in escalation clauses based on the PPI. I am trying to build a report that lets me view the expected income for the remaining contract period but the calculation seems way too clutzy and this only works on a 60 month contract so I have to render further case statements for 48 months, 36 months etc; Fields; Months (the duration of the contract) Elapsed Time (The number of months already past in the contract) Remaining Time (The number of months l…

I have come up with a timestamp auto-enter calculation that updates conditionally only if a value in certain fields change. For example, Table I has fields a, b, c, d, and e. This mod timestamp should update only if the value in b, c, or d changes (it will be a separate field from the housekeeping mod timestamp of the table that updates on all changes). I've tried something like: Let ( ~trigger = b or c or d ; If ( ~trigger ; Get ( CurrentHostTimestamp ) ; Self ) ) This appears to render the desired behavior. I am most interested to get the opinion of some experts about whether this is a safe method for accomplishing this. Are there any gotchas I should be…

Using Filemaker 11, with MacBook Pro running System 10.6.8 I have just exported 2000 records. They are all there but only one of the layouts is present and not the one I use routinely. How do I get export to export my layouts as well so that I can view my exported records on another machine running FM11 in the layout I usually use?

Hi, We have a Ticket Booking application using FileMaker 12. hers is one of the chain os tables Parent >> Child1 >> Child2 There are few calculation fields in Child1 which are either count or sum of fields in table child2 and hence those turn as unstored calculations. next these unstored calculations fields of child 1 are referred - sum up in the parent table and parent table also has unstored calculation fields. On one of the layout which has portal of child 1 (with unstored calc fields) and also the unstored calc field of parent table, it takes time to load and sends application in not responding mode. It further aggrevates the problem when user s…

I have 4 fields defined as miles, rate, gross_amount, volume_amount and flat_rate. The gross_amount field needs to calculate this scenario: if miles has a value then miles * rate if flat_rate has a value then flat_rate if neither has a value then volume_amount (which is a calculation field) needs to return volume_amount * rate. I have tried different scenarios but not able to make it work. Case ( IsValid ( flat_rate ) ; flat_rate ; IsEmpty ( flat_rate ) ; (miles * rate) ; volume_amount * rate )

I have a text field containing reports imported from an electronic patient record DB. These reports have tons of open space between paragraphs etc, making reading difficult (much unnecessary scrolling). There are no excess space characters, and trim() has no effect (many records start with multiple empty lines). Presumably there are invisible <CR> characters all over the place, but I can't strip them out with substitute (field; "¶¶" ; "") or anything similar. Any suggestions on how to edit invisible characters with the calc engine? thanks!

Has anyone seen this? http://cloud.zerobluetech.com/image/1C421A0p0F47 Is my field corrupt? See how due2 returns the proper value. I manually copied the date from due to due2.

Hi, all: Many of us are familiar with the quick-'n'-dirty way of seeing duplicate records by using a self-join table with the primary key as a reference and the connection between table occurrences using the field of question for comparison; it also uses an unstored calc similar to this: Case ( _kpln__TableID__lxn = Table 2::_kpln__TableID__lxn ; "Duplicate" ; "" ) ...and in this example, the field to be checked is a text field containing a color (red, blue, green, etc. See attached.) My question is, if there are two (or more) records with red in the test field, why does the Duplicate calc only see one of them? I would think that every record with red in t…

I have a filemaker 6 database file called "Drawings". Each record represents one architectural drawing, using a "Picture" container field to store the picture. The container field stores a reference ("Store only a reference to the file") to a .jpg picture file, not the picture file itself. The container field diplays the picture fine immediately after it is added. However, at some point (usually days later), it will stop displaying, showing the "Could not draw picture file" error message. - The source picture files are not being renamed, deleted, or moved in any way. - The problem occurs whether the database is hosted via FileMaker server, or accessed local…

Hi, I have an invoice table with the following fields ... Days OverDue and Invoices OverDue Days OverDue: ( Basically a positive, or a zero, or a negative, or empty if text ) If ( IsEmpty ( Filter ( Terms ; "0123456789" ) ) ; Date Delivered - Search Date ; /* Field Terms could have Text - Immediate / Upon Receipt */ If ( Filter ( Terms ; "0123456789" ) ≥ 0 and Search Date ≤ ( Date Delivered + Filter ( Terms ; "0123456789" ) ) ; ( Date Delivered + Filter ( Terms ; "0123456789" ) ) - Search Date ; ( Date Delivered + Filter ( Terms ; "0123456789" ) ) - Search Date ) ) Invoices OverDue: ( Basically if above; Days OverDue is zero or below to…

One of my primary keys is in the form of XXX-YY (XXX:three digit number, YY: last to year digits) which doesn't lend itself to sorting. So I am reversing it to YY-XXX via a left,right and & calculation. How do I keep these values, as values only, not as outputs of a calculation so I can delete the original pk field?

I've two timestamp fields, one is "Start" and the other "End"... I've a third field called "Second difference".... What calculation should I use on the latter in order to get the seconds difference between each of the timestamps? Example: Start: 2013-08-01 14:45:04 End: 2013-08-01 14:46:10 Second difference: 66 <-- this is what I need And in some cases the End timestamp crosses to the next day, example: Start: 2013-08-01 23:58:30 End: 2013-08-02 00:02:15 Second difference: 225 <-- this is what I need I tried simply End - Start but it gives me a "?" as a result..... I also tried Seconds (End - Start) but same thing...

We have a text field ( text ) with 2 rows of text: the first row is aligned to center and the second row is aligned to right. first row second row Which is supposed to be the result of: GetAsCSS ( text ) with FMP12 The result isn't consistent, sometimes is: <SPAN STYLE= "text-align: center;" >first row<BR></SPAN> <SPAN STYLE= "text-align: right;" >second row</SPAN> other times is: <SPAN STYLE= "text-align: center;" >first row<BR>second row</SPAN>

Hi folks, I've got a portal that's used to mark pupils as present / absent etc. I'd like to count the number of "presents" so I can work out attendance rates. I've had a go at a script that's triggered every time the attendance field is modified (attached). This updates a field in the same table so that the results can be compared with the summary count. Does it seem like I'm going this right? It's not working at the moment; hopefully it's a trivial mistake I'm making with my script! Cheers, Mike Â

I am building a schedule generation database, and I'm trying to figure out an efficient way to identify schedule conflicts between classes. Each class can have up to 5 meeting times. I've got a way that works, but it's very brute force and I think there has to be a better way. Currently, I've got a Student in Classes table related to itself by student ID and a non-equal Student in Class ID. In other words, they will connect for all of the classes that a student is in other than the particular class being referenced. Through this relationship, I'm checking for class time conflicts using this calculation: Case ( sgClassTimes::timeStart ≥ sgClassTimesCONFLICT::ti…

is there a way to adjust the text position of the highlighted text using a script

I'm trying to simplify a manual calculation I'm having to perform right now. The company I am with pays employees a day rate ("rate") rather than hourly. No matter what the employee is guaranteed 12 hours of pay per day (sometimes the day rate is for 10 hours). The rate needs to be broken down into an 8-hour ("first 8") hourly rate + time and a half ("remaining") for the remaining 4 hours (or 2 hours if the rate is for 10) to equal the overall day rate. The duration of the day ("duration") is a set of radio boxes. How can this calculation be set up?

I have a database that consists of purchase orders (one record per PO) that will have a description assigned by a checkbox. I then have an "over view" sheet for each job that will give the end user a quick idea of what type of products are assigned to that job. So, if a job has 5 purchase orders, and the descriptions for the purchase orders are "A, A, B, D, D" I would like the check boxes on the over view sheet to be checked as "A, B, D" with C and E remaining unchecked. Also, a purchase order can be "A, C and D", or any other variation. What I have done so far is assign for each purchase order a calculation to determine whether or not a value is checked (If (…

Hello All, Hoping one of you fine folks can help me with a strange little problem I've got. I'm trying to create a field that you can type a value into once, and have it show up as that same value on every single record in the table. I tried just doing a global field at first, but I quickly found out that doesn't work with shared files. Silly me for actually expecting a global field to work "globally". Next I tried making a value list based on the index of said field, and then displaying it with an unstored calculation defined as the contents of that value list. I thought this was a pretty good workaround, but it turns out that it affects the performance of th…

a. I am having a hard time understanding the difference denoted in fmp help wrt to the following: "For Validation options for this field, select Always for ongoing field validation, or Only during data entry to limit validation to instances when data is being entered." b. I am setting a field to Date type. And fmp also offers the option of a "strict data type" which I can set as 4 digit date. I am not sure what this adds to the validation process since the field is already in a so to speak strict data mode via fmp's default 4 digit date format. Surely, it does adds something, but I can't figure out what.