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,611 profile views
  1. That's incredibly helpful - glad to know that I'm not the only one hitting this kind of challenge. (Although the other poster described it far more accurately than me) I've downloaded the demo file, and to be honest that will be enough to give to the administrator to experiment with. It'll certainly save a chunk of time compared to the manual method. I could probably find a way of integrating the capability into our main solution (and being able to automatically copy in the totals from the unreconciled orders) but it's overkill unless they are going to use the core functionality.
  2. I've had an interesting challenge thrown in my direction by one of our administrators. Part of their job is to reconcile credit card payments that we have received, against orders that are in Filemaker. 98% of the transactions are simple to reconcile, but the remainder can be problematic. This is often caused by other staff not marking FM orders as having been paid, but nonetheless it still needs picked through. The problem is compounded by the way we receive card payments into our bank account, there are usually a number of payments lumped together for the day(s) in question. This makes it hard to spot unreconciled payments. If there is one individual payment for, say, £45 landing in the account, and an unmarked order worth £45, that's easy to marry up. Where it gets more complicated is when there are three or four unmarked orders, and all (or none) of them could be attributed to the one composite payment that has landed. What I'd like to be able to provide for them is a calculation function that shows how different combinations of totals would match with the payment. Here's a grossly simplified example: Unmarked order 1: £5 Unmarked order 2: £10 Unmarked order 3: £15 Total received: £15 Anyone glancing at this can tell that the total received could have been made up of Order 1+2, or Order 3. It's much harder (and more time consuming) to work it out manually when there are multiple larger, irregular numbers to consider. I'd like to automate this process for them if possible. I can think of a script that plods through each potential combination, but this is going to get exponentially more cumbersome and very time consuming to write. I'm guessing that there must be a more elegant way of doing it, but it's well outside the areas I'm familiar with. I suppose it's essentially a math problem, which is not one of my strengths! Can anyone suggest a good way to attack this?
  3. The database that I maintain at the moment has a simple text field for “Order Notes” which is used for various miscellaneous notes that don’t fit into any of the other fields. It’s a bit of a “catch all” receptacle. A couple of problems have emerged recently. One is that staff have been relying on notes that have been written quite some time ago, treating it as “gospel” when in some cases it has been superseded. What I’d like to do is have an indication on the field of when the information was input. It wouldn’t be hard to add a timestamp that is changed when the field is updated. The drawback I see with that approach is every alteration, no matter how minor, updates the timestamp, so we could have a recent timestamp, but old information, just because someone tapped the spacebar in the field. What I’m thinking about is storing each iteration of the Notes text as a separate record in a related table. There would be still only be one notes field visible to the user, but the various versions could be checked through if required. It doesn’t seem too complicated to have a script trigger when the information in the Notes field is committed, that copies it into a new related record. Then some form of portal to allow users to quickly flick through the versions. But I have a nagging feeling that this is a bit of a bodged inelegant way to go about it - is there a more straightforward way of achieving this that I’m missing?
  4. 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...
  5. 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?
  6. 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.
  7. 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.
  8. 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...
  9. 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.
  10. 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?
  11. 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.
  12. 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?
  13. 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.)
  14. 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.
  15. Thanks, that looks perfect. It'll also be a more elegant way of doing the date headers at the top of the layout too.
  • Create New...

Important Information

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