Jump to content

Angus McKinnon

  • Content Count

  • Joined

  • Last visited

Community Reputation

0 Neutral

About Angus McKinnon

  • Rank

Profile Information

  • Gender
  • Location
    Glasgow, Scotland

FileMaker Experience

  • Skill Level
  • FM Application
    14 Advanced

Platform Environment

  • OS Platform
  • OS Version

Recent Profile Visitors

1,440 profile views
  1. Thanks for that link - seems like a very sensible way to tackle it. I'm imaging that the best way to proceed is have a script set variables from the summary values out of the filtered portals (using GetLayoutObjectAttribute) and use that to populate the summary table. Let's see how I get on...
  2. Here’s the scenario. I’ve got a “Monthly Report” layout in my database, which has a collection of portals showing There is a “Monthly Report” table which is linked to Orders via a many-to-many relationship. Each portal then filters based on multiple criteria, a combination of the date range and division/category. This way rental is separated out from sales income etc. etc. I’ve set up summary portals as well, using the single-line technique that I learnt from another thread here. So far, this is all working well and the admin staff are finding it really useful. Naturally, this makes them ask for more features. The latest request is to be able to see the performance, month-by-month, on a graph. I’ve been playing a little with graphing and can grasp the basics (after a few amusing mis-fires). However the tricky bit is how to deal with the summary values. I’m planning on setting up one record per month in the “Monthly Report” table which has the start and end dates set as fields. At the moment the summaries are created “live” by the portal filtering, so presumably only exist when that filtering is live, in front of the user. I can use a Get statement to copy the value out of the layout object, but I don’t imagine that’ll work unless I use a script to go through each record in turn, which seems cumbersome. Am I best to just come up with a calculated field of some kind which replicates the filtering for each portal? Or is there some smarter technique that I’m blissfully unaware of?
  3. I know that this is an old thread, but I wanted to chip in and thank David and Bruce for their work on this function - it's solved what would have been a fairly major headache for me. (I'm doing a count of total spend by customers, but only orders that met certain conditions) The only other ways I could think of making it happen would have been an ugly bodge.
  4. Got it - I think! I was trying to detect a match between the Filtervalue result to the Order ID of the line item in the portal. It figures that if there isn't a match the result will be empty so no need to to worry about equivalence. That's a much more elegant way of working it. It'll be a day or so before I can get back to this but it'll be the first thing I try, hopefully that'll be it.
  5. I've only just been able to return to this task. In theory it should be simple enough to implement, but I suspect I'm doing something wrong with my syntax. (Yet again) I've got the global text field set up (called "Orders to Omit") and there's a button on the portal which appends the OrderID of that item onto the list. The problem is the filtering. To try and simplify things, I've put a calculation field in each item record, using an IF statement to return a "Yes" or "No". However using Filtervalues as below doesn't seem to work. The calculation returns "No" irrespective of whether the OrderID is in the "Orders to Omit" field or not: If ( FilterValues ( Item_Planner::Orders to Omit ; GetAsText ( OrderID ) ) = GetAsText ( OrderID ) ; "Yes" ; "No" ) I also tried using PatternCount as an alternative: If ( PatternCount ( Item_Planner::Orders to Omit ; GetAsText ( OrderID )) = 5 ; "Yes" ; "No" ) (All of our OrderIDs are five digit numbers so this should give me a match) I added the GetAsText function because I realised that the OrderID field is a number, but it doesn't seem to make any difference in either version of the calculation. I'm hopeful that it's just another simple blunder, but I'm not sure if something else might be afoot...
  6. In theory, it's not long at all. The omitted record IDs definitely don't need to persist between sessions. The actual scenario is that the user searches for upcoming availability on an item, gets taken to the planner layout, and may choose to remote a record or two. The state of the portal will only be around for that single view which is likely only to be open for the minute or two it takes them to decide that there is enough availability to approve a hire. I'd prefer to purge the omitted list quite promptly, so that they aren't accidentally kept out of other searches. At the moment, we don't have the volume of users for this to be much of a problem but it wouldn't take much to cause confusion. Just thinking, I'm using a served solution.Does a global field or variable share the same value between users? If it doesn't, then my concern about accidentally affecting other users isn't warranted. Just found this in the FM help. So it looks like I don't need to worry about different users clashing.
  7. I've been gradually working on a planner layout, which uses a portal and conditional formatting to display availability of hire items. It's working well so far, and is proving popular with users. At the moment, I'm using portal filtering to narrow down the selection in the portal, typically to just one product/item. Each occurrence in an order is displayed in a portal row. What I'd like to be able to do is temporarily exclude an item from the portal results. Sometimes the query used to filter the portal is too broad, or we have a very similarly named item that turns up when it isn't relevant. In an ideal world, the user would hit a button on that portal row, and it would disappear (and would not be counted in the various summaries etc. that we use). If I was dealing with a found set of records in list view, I'd simply script a button to Omit Record and that would be that. There's not an equivalent behaviour within a portal since it's based on a relationship. I'm already filtering the portal and could add a condition to that formula to facilitate this, but I'm struggling to think of an elegant way of achieving it. What's simplest (at face value anyway) is having an "omit" field on each record. Pressing the button toggles that field, and the filter takes care of the visibility. I can think of two drawbacks. If one user toggles the field, then it will affect other users too. Also, the field needs to be "reset" afterwards. The table has 40k+ records so a simple looping Set Field step isn't going to be particularly efficient. The possibility I can think of is to approach it from the other angle. Each record in the portal has a unique ID. The button could copy that ID into a global field on the enclosing table, and this field is used for the filter instead. The advantage is that I've only got one field to clear/reset afterwards. I can just append multiple IDs to the one text string in the global field and use a patterncount in the filter formula. This still feels a little clumsy though, is there a better method that I'm missing?
  8. That's useful, and a very elegant way of doing it. I confess I'd got them categorised as a bodge from pre-relational times that is best avoided. But they undoubtedly have their place. I've managed to get the layout finished with much less overhead than I was fearing - thanks mainly to the advice here.
  9. Agreed - it's more how I was visualising the calculation itself. Interestingly there don't seem to be many examples of this kind of calculation in all the various blog posts and forum threads that a google search turns up. It's all working fine now, just a few more tweaks to the layout before it's finished off. At the moment the date and day headings at the top of the layout are multiple individual fields. (Seven fields for the days, and five for the dates). With my new-found knowledge I'm tempted to re-do these as repeating fields. It would certainly make the table neater, but I've already done the work with the existing fields. Are there any performance advantages to having, say, one repeating field with seven values, rather than seven individual fields as I have just now?
  10. Excellent, thanks Comment. The crucial part I'd overlooked is that every time a non-repeating field is used in a repeating calculation it needs to be wrapped in Extend. I hadn't realised that it applied to every mention of the field, not just for what I was thinking of as the definition of an array. So my original attempt was this: If ( (Item_Planner::Planner_date_array ≥ Date Out and Item_Planner::Planner_date_array ≤ Date In); Quantity; 0) Whereas the correct syntax is this: If ( (Item_Planner::Planner_date_array ≥ Extend(Date Out) and Item_Planner::Planner_date_array ≤ Extend(Date In)); Extend(Quantity); 0) (It took me a while to realise that Quantity needed to be extended as well, otherwise the calculation just returns a blank value. This is because, without the Extend function, Quantity has no values beyond the first repetition. The way I picture it in my head is of a grid, with repetitions increasing along the X and Y axis. The calculation can only proceed diagonally, and without Extend a non-repeating field is empty after the first row.)
  11. Partial success - I suspect my inexperience with Repeating Fields is the problem. Using Comment's code snippet above, I've defined a repeating field (in the Planner table) called Planner_Date_Array. This seems to do what I'd expect, if I display all the repetitions the dates are there as anticipated. Then, in the Order_Item, I've defined a calculation field (which I've called Quantity_by_date) which is based on an IF statement to see if each repetition of Planner_Date_Array overlaps with the hire period. The syntax is simply If ( (Item_Planner::Planner_date_array ≥ Date Out and Item_Planner::Planner_date_array ≤ Date In); Quantity; 0) The Quantity field is just a number field that is set by the user when booking in the hire. So if we have a line item for two items, hopefully the Quantity_by_date field should return "2" on a repetition that corresponds with a date which overlaps the hire period. Then I've used a Summary field to return the total of Quantity_by_date and am using a duplicate portal to display it. The problem I'm having is that the first repetition of Quantity_by_date works, but all the subsequent repetitions just return a zero. Similarly, the Summary field returns the total that I'd expect to see in the first repetition only. I suspect I'm doing something wrong in the calculation but I'm a complete novice at handling repeating fields so may have overlooked something glaringly obvious.
  12. Thanks, that looks perfect. It'll also be a more elegant way of doing the date headers at the top of the layout too.
  13. This sounds very promising, thanks. (I've not really done much with repeating fields since early bodges in v6) Am I right in thinking that I can define a repeating field with let's say 40 repetitions, one for each column in the planner. Each value is just a serial number counting up. So then I define a calculation using (Planner_start_date + repeating_field) in the formula. Will that then give me 40 repetitions of results, one for each day on the planner? If so, that's going to save a lot of organisational overhead and manual definitions. Is it likely to be quicker for Filemaker to execute, too?
  14. I've been working on a planner-style layout as part of a rental management package that I haphazardly develop. The idea behind the layout is to let users see, at a glance, how availability is looking for a particular item. There's a screenshot of it attached below. The items displayed are all records in my Order_Item table, they are essentially "line items" on a hire invoice. The planner itself is based in a separate table, with the items related using a Link-all arrangement. Then the portal is filtered to include only the relevant items. (The date range is constrained, and the item name is filtered via a PatternCount function) The blocks of colour that you see are done with conditional formatting, each portal row is a long button bar with different formatting applied to each individual button. (Essentially adding a day to the portal start date for each column in the chart). I figured this was better than having 40+ calculation fields on each record in the Order_Item table. So far, it seems to be working quite well (if a little slowly, but that's hardly surprising given the amount of crunching going into the display). The next step is to hopefully display the total number of each item required, on each particular day. This has got me scratching my head. Each line item has a quantity field, as customers can choose to hire any number of an item. So someone could casually eyeball the chart and not realise that one line of blocks represents two, or more, items. I'd ideally like to have a row at the bottom showing a summary with the total required. I've read up on portal summaries and it would seem that the best solution is to create a separate portal, with the same filtering, that displays a summary field on only one row. That seems straightforward enough, but the calculations required to make this happen seem quite demanding. First I'll need to calculate whether each line item is reserved on that date, for every column in the planner. And then a summary field, for every column too. I could go ahead and code all this, but it's going to be quite a bit of repetitive work to produce it. And I suspect that the amount of calculation involved (on top of the conditional formatting already present) will make the layout horribly unresponsive. I can't help but feel there's a better way of doing this, but I'm stuck for ideas. Does anyone have any suggestions?
  15. Just to "close the loop" on this, I've given up on getting the autocomplete to work, as I'd originally hoped. However going through the process of attempting it has sparked off some ideas for a better item picker layout, which the users far prefer. Rather than trying to encourage them to correct the item names as they go, I've got a script set up so that the administrator can quickly skim through any items that aren't properly set and marry them up with products. The end result is altogether better than it would have been if I'd been able to wave a magic wand and fix my original challenge. So the process has definitely been worthwhile.
  • Create New...

Important Information

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