Jump to content


  • Content count

  • Joined

  • Last visited

  • Days Won


Everything posted by madman411

  1. Thanks, Comment. This worked a charm.
  2. Trying to trim characters from both the left and right of a value in one field. There are no breaks or symbols within the value. For example, the following value is in my "full_ticket_number" field: 2900686073681910 In a separate calc field I need to have the "29" from the left and the "0" from the right removed to provide the value: 0068607368191 In this example, the "29" and the "0" seem to be some sort of control character placed there by the issuer, which have no use for me in my solution. Of course the value is unique for each record so I can't just have the calculation look for certain characters to apply the function to. I've tried to combine both the Left and Right functions, but FM is providing errors I can't seem to solve. Advice much appreciated!
  3. Hello All I have been playing with the free Airline seating demo file from SeedCode. I think this is great and I'm looking to modify it in a way that allows for multiple aircraft configurations, but I'm hitting major snags. For the record, this is just a personal project and in no way is being distributed or sold. If you look at the attached file, they've used repeating fields to create the seat map. The repetition is captured and turned into a seat assignment. Thing is, the seat map used in their file in no way mimics a real-world commercial aircraft and there doesn't seem to be a universal way to map seats among multiple variations of aircraft configurations without extensive calculations. As an aviation enthusiast, I modified the seat map in the FRONT file to replicate a common Boeing 777 configuration with three classes, but as of now this only works properly when there's only one specific seat map. Rows 1-12 are First Class, with a 1-2-1 seat configuration. 17 rows are "skipped" and Economy Comfort are rows 29-32 in a 3-3-3 seat config. Rows 33-57 are standard Coach in a 3-3-3 seat config. Using their existing calculation set up, repetitions 1-48 accurately calculate to the correct seats in First Class. To then map Economy Comfort correctly you then have to skip to repetition 253. That's 205 repetitions completely ignored. The part where I'm hitting a brick wall is integrating additional aircraft seat maps for other "flights", such as an A320, which has a completely different seat map configuration. I don't see how this existing table/relationship/script structure would allow for the database to track more than one map. I've separated the aircraft map into a separate window and considered different layouts for different aircraft seat maps linked to different tables. Each aircraft seating map using a different script, however, the passenger information part of the database would need to be consistent, no? Also, with their current script, dragging a First Class passenger to an Economy Comfort seat yields a seat number which is completely off. With their current config and a larger aircraft I'm struggling to get FM to calculate remaining/unassigned seats in each cabin leading me to believe this might be easier using records rather than repeating fields. Any input appreciated AirplaneSeatingDemo.fmp12.zip FRONT.fmp12.zip
  4. Hi All I'm running into a bit of a calculation dead end. I have an "order" table displayed in a portal where my users can create orders. The user can add a group of records (a kit) from the inventory table. The first related record is a "master" record which shows a description title and a subtotal (see attached image) and where I would like the user to be able to adjust the quantity for the overall group of records. When initiated the script assigns these related records a GroupID which is currently set as a self join (OrderLine::GroupID = OrderLineKit::GroupID). The system calculates accurately if the master record quantity is set to 1, but if the user increases this quantity to two or higher I'm getting the numbers overlapping (stacked fields) and then the user can't manually adjust any of the other quantities without some strange results due to my current calculation. Some grouped records are added with pre-existing quantity amounts which need to be doubled/tripled, etc, if the user increases the master quantity, however the user should still be able to manually change any quantities. The quantity field is where it's driving me crazy. I have the following fields that are stacked: Qty (calc field, hidden on master record row) KitQty (hidden on all portal rows except for the master record row) QtyOR (quantity override, hidden on master record row) When the group of records is added it populates the quantities in the QtyOR field. Currently my Qty field has the following calculation: If ( isEmpty ( OrderLineKit::KitQty ) ; QtyOR ; QtyOR * OrderLineKit::KitQty ) I know my script is missing criteria which is giving me these issues. I hope I explained myself well enough for people to understand. The below image shows an example group of records with the master quantity set to two. The prices and such update properly, but there's an overwrite issue (Qty and QtyOR data displayed on far right for example purposes). Aside from this problem my system works just as expected. I'm so close to wrapping up this part of my database!
  5. Hi all I'm trying to create a calculation (order::list) to list related records that have the value of "1" in a field in the related table. My tables are "order" and "order lines". I use a portal for "order lines" on my Order layout. Right now they're connected by Order::OrderID and OrderLines::OrderID. Two fields in order lines table are OrderLines::Description and OrderLines::Flag. If the flag field is "1" I need the list field to list what's in the description field with a carriage return for all related records where the flag field is 1. If it's 0 or empty then that record needs to be ignored. The List function works fine to return the contents of the Description field of all related records, but I'm struggling to make the calculation only look for certain values. I'm sure this is pretty simple but help would be much appreciated. Thanks!
  6. The system is connected to QuickBooks. The system subtotals on certain rows so when my boss pushes his orders to QB to create an invoice he only wants the information on these rows passed through.
  7. Hi guys I've created a small database to track my hours as an independent contractor in the entertainment industry. I have everything working great and want to implement one more feature into my time calculations, which I think is a little more complicated. I have been doing this manually so far and then adjusting my "Total Pay" with an overwrite field. Currently I'm tracking time as decimals (i.e. 1:15PM is 13.25, etc). In my industry we have what are called meal penalties which an employee can start incurring if they haven't been broken for lunch within 6 hours and again if they're not broken for a second lunch 6 hours after the first lunch ended. 1 (one) meal penalty accounts for a 15 minute block of time, so if you start at 6am and aren't broken for lunch until 1pm, thats four meal penalties. If you again aren't broken at 7pm, respectively, you then start incurring more penalties until they either break you for the 2nd lunch or wrap you, and so on. Typically it's more common for meal penalties to be incurred at the end of the day (they make you work while eating your second meal) and we'll rack up meal penalties up until they finish us for the day ("wrap"). Ideally, I'd calculate how many meal penalty blocks I've incurred as a standard number. 1, 2, 3, 4, 5, etc... Currently I have the following fields calculating my days: ((Invoice Terms)) Pay Rate ((TimeCard)) Call, Meal 1 Out, Meal 1 In, Meal 2 Out, Meal 2 In, Wrap, Total Hours, Total Pay I want to add: ((Invoice Terms)) MealPenalty Rate ((TimeCard)) MealPenalties Incurred And then I can add the meal penalty pay into the total pay appropriately. My main concern right now is just calculating the blocks of time. Any help in calculating this penalty dealio my industry has so gratefully provided us as contractors would be great!
  8. Calculating blocks of time

    Alright. I think I have it, but I've become a little dazed while trying to debug my calculation while thinking in standard time. As usual I overcomplicated things in my head... Comment, please grade: Let ( [ period1dur = Min ( Meal1Start ; Meal2Start ; WorkEnd ) - WorkStart ; penalty1dur = Max ( 0 ; period1dur - 6 ) ; period2dur = Min ( WorkEnd ; Meal2Start) - Meal1End ; penalty2dur = Max ( 0 ; period2dur - 6 ) ] ; 4 * penalty1dur + 4 * penalty2dur )
  9. Calculating blocks of time

    Comment Works a charm! I can definitely work off of this. Would you suggest calculating the second block of time in a separate field?
  10. Print two tables on one report

    I've created a database that automatically complies an invoice for time worked. I'm loving how it's working. When trying to generate a report I need the report to include two sets of information (from two different tables), ideally in two separate parts: - TimeCard table - Reimbursements table Both tables are linked to a primary table called Invoicing by the InvoiceID. Creating a report that displays the information individually is easy - I select either the TimeCard or Reimbursements table the Layout Setup window, set to view as list, place the desired fields within the body part setup, and bam. Is there a way to do this without the use of a portal to display the second set of information? Ideally the report resizes itself based on how much related data is being displayed. I don't want to account for 10 portal rows to display related reimbursements if there are no reimbursements. Thanks
  11. Hi everyone I've created a window that opens when the user chooses to print a report. I would like to give them options on how to generate this report (or which layout to go to depending on their selected options). The client is very picky about choosing what information they want available when they print certain reports. Of course, I want to allow them a few options to choose from so they can provide more/less info should they choose. A few of the options I've provided as checkboxes in a global field: - Show line amounts - Show line comments - Include order disclaimer - Show system order notes etc... I know a script using IF statements could direct FileMaker to the correct layout to print, but I feel this could be a very hefty script and quite hard to keep track of when using "show line amounts" and "show line comments" as the results. Perhaps there's a way to calculate the correct layout using a numerical result? Thanks!
  12. I have a field that displays a "version" in my invoices. All new invoices start as version "A", however when a certain script is triggered I need the version field to advance to "B", then to "C" if it's triggered again, and so fourth. I thought if I could capture the current version as a variable and then set the Version field as $version + 1 would do the trick, alas I was incorrect. What's the best way to script this? Additionally, should the Version hit letter "Z" I would like the alphabet to start over again as doubles, such as "AA", "AB", "AC", etc... Thank you! === Update: So some tinkering and I got this calculation to achieve what I want. Using Set Field: Char ( Code ( $version ) +1 ) However, after "Z" I start getting other characters, like "[". Is there a way to prevent this like I mentioned in my previous post?
  13. Advancing letters when script triggered

    Thanks comment. This did cross my mind. Do you mean the entire order is "duplicated", child records and all (related 'line items' within the order)? Is there a link you can share that can explain a little more? I would've thought this would be a hefty script...
  14. Desktop shortcut

    I remember a while ago looking into creating a desktop shortcut to a file hosted on a server machine and it was a little more complex than I expected it to be. Has anyone had luck in creating a desktop shortcut that opens a FMP file over the network? I'd also like to use the icon I created rather than use the FileMaker default. I would need to create a shortcut on Macs and Windows computers.
  15. Using the Hide Object When calculation engine feature I'm attempting to hide a button no matter what the state of the layout... Get ( WindowMode ) = 0 or Get ( WindowMode ) = 1 The first function occurrence works, no matter if I'm referencing 0 or 1 as a result, but the second function doesn't seem to work at all. Is OR the wrong expression here or is the calc engine expecting an IF statement? I have some hidden off-layout pop-up buttons that seem to appear when the user enters Find Mode (when I use 0 as the first result). I want these buttons to be hidden in every mode except layout mode.
  16. Get(AccountName)

    I'm trying to set one of my custom menus' label to be the Account Name, however I want this to appear in Uppercase, or possibly even title case if I decide. I've tried using TextStyleAdd ( get ( accountname ) ; Uppercase ) however it doesn't do anything, leading me to believe custom menus can't have styles added to them, which I suppose makes sense. I've also tried creating a new global field and applying the style to that field and using that field as the title override for the menu - nada. Any ideas on making this work?
  17. Get(AccountName)

    Thanks for clarifying, comment. Thinking back, this type of text modification within FM is something I've tried to suss out for a while but it seems I've been looking in the wrong place - the Text Formatting function list within the calculation engine.
  18. Get(AccountName)

    Works a treat. I would've initially thought that Upper() and Proper() are essentially text style/formatting functions as well? Needless to say, it's exactly what I needed.
  19. Filtering via relationship

    Hi guys I have a couple portals that are filtering via the portal rather than the relationship and and wanted to see if someone could recommend a way of changing this to filter purely by the relationship. For example: I have a company layout that has a portal to display each of the following: related invoices, related purchase orders and related projects. In this post I'll use Invoices as an example. The initial relationship does filter down to the related company record (company::id = invoices::id_company) however, I have multiple other filters that need to be applied, which is where I'm having the portal filter the results at the interface level. I have a series of checkboxes to further filter the results shown (in terms of invoices: open, in process, void, completed, closed, etc) which references the Status field within the Invoices table. Currently, multiple options can be checked and the portal will filter the results respectively. This is a global field within a separate table (invoiceGlobals::gInvoiceFilter). Initially I don't expect there to be many related records, so filtering via the portal is relatively fast right now, but as more related records are added I'm anticipating the portal needing to filter many more, especially since there are two other portals on the same layout performing the same task. I guess the point is, is there a way to filter all of this through the relationship graph? I'm a little stumped because there's multiple criteria. Cheers!
  20. Filtering via relationship

    Scratch that, comment. I created a test file and it works perfectly. Many thanks.
  21. Filtering via relationship

    Thanks comment, I will test this out when I next have access to the database. My only concern is if multiple check boxes are selected. For example, if "open" and "void" are both selected, then only open and void invoices will display in the portal. If I remember correctly I had to use a function in my filter calculation... I believe it was FilterValues - will the relationship graph be able to decipher two or more values in the same field at a time?
  22. SQL sum issue

    I'm trying to sum a set of related records that only contain a specific value: ExecuteSQL ( "SELECT SUM (Subtotal) FROM LineItems WHERE id = InvoiceID AND sFlag = 1" ; "" ; "" )but I'm getting a null result. The Subtotal field is a calculated field. Ideas?
  23. Subsummary in portal

    Anyone achieved this? I want to add [a] line(s) to a portal and have it sub-total the lines above it. The portal has drag-n-drop functionality, so the calc needs to update if a related record is "dragged" above the sub-summary line. I found the following article, but I'm having a hard time following it because the author is using fields in relationship joins that he never explains. http://www.linearchat.co.uk/2011/05/sub-summarys-with-in-portals-pt-ii-may/
  24. Subsummary in portal

    Thanks anyway
  25. Subsummary in portal

    Thanks, comment. There was a step I overlooked that I discovered when reviewing your example file. Your example is a massive help. I suppose the only other thing I would want your opinion on is re-generating the break field. Your example is a bit different from what the original article was describing. Using the Case() function I was able to get the subtotal down to display on the subtotal portal line only, and that works like a charm... Case ( subFlag = 1 ; ( SummaryField ; BreakField) )The original article talked about being able to re-arrange portal rows and the subtotal calc and breakfield would update to reflect any new portal rows added/moved between a previous subtotal "line" and the next. He appeared to create two self joins to be able to do this (judging from the screen shots he posted). One for the sort and one for the subtotal calc itself. The following code is what he provided his readers which I'm assuming is what was supposed to generate and and recalculate what would be the break field: Let ( [ L = List(QuoteLine_QUOTELINE__sort_calc::__kp_QUOTELINE); p = PatternCount ( Left ( L ; Position ( L & "¶" ; __kp_QUOTELINE & "¶" ; 1 ; 1 )) ; "¶" ); s = List ( QuoteLine_QUOTELINE__sort_calc::d_Sort) ; v = GetValue ( s ; p ) ]; Case ( p = 0 ; 0 ; v ) )I suppose my question is how could we modify your file to calculate the breakfield (or category field) instead of manually entering a value? In my file the drag and drop feature updates a sort field, so perhaps the breakfield needs to be able to reference that?

Important Information

By using this site, you agree to our Terms of Use.