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,147 profile views
  1. Thanks, I'll give that a try. The number of products is unlikely to exceed a couple of hundred so hopefully won't cause too much of a slowdown.
  2. I can get the PatternCount method working in a portal, but not as an autocomplete. I've given up on getting an autocomplete happening the way I'd like, it's just a case of what's going to be best for whatever alternative solution we end up using. Having a portal with the "correct" choices appearing alongside would probably be the simplest option at this point I think, unless there's some other possibility that I'm overlooking.
  3. Alas, we're not on FM16 yet, and to upgrade we'd also need to replace quite a few of the client machines, so it's probably out of contention for now. We will need to make the jump eventually though - I'll keep it in mind.
  4. Thanks, that's very useful to know. Do you think it's better to base a relationship on a partial match, or stick with the PatternCount method I've been using? I also now need to decide what sort of alternative method I can come up with for nudging the users towards the correct names. At the moment the items are added in a portal from the main orders table, and I wonder if I'll have more options available if I make a new layout based on the items table? That would, for instance, allow me to use a portal showing matched terms since the autocomplete is no longer an option.
  5. Thanks, comment - it's useful to look through the file and see how it's been done. My problem is that I'm trying to do more than just a straight match relationship. The goal is to catch when users are entering an "incorrect" item name manually, and use the autocomplete feature to steer them towards the "correct" item name. This gives us the consistency that we need (to be able to search on items to work out availability etc.) but also doesn't upset the current workflow too much. If I can implement it as I'd hoped, it should actually speed up data entry for users. Autocomplete as standard only works with the start of a string, so, using your example, if the user starts typing "hand" it'll offer "handle" but not "door handle" as suggestions. This is where the calculated field using PatternCount came in, as it would match anywhere in a string. But this unstored calculation seems to be what's making the relationship unavailable.
  6. Thanks - I'll spend some time digesting both links. I'm only needing to search based on one field which hopefully simplifies things somewhat.
  7. I've tried a few other approaches and workarounds with this, but am still stuck with the same problem. I can get the results I want using a filtered portal, but this lacks the convenience of autocomplete. A calculated field (based on the patterncount function) can't be used in a relationship. The one other workaround I came up with was triggering a looping script, which inserted a calculated result into a field in each record on the Products table. This field is then used as the match field for the relationship. This allows the value list for autocomplete to be set up, but it's a very convoluted method and has a number of drawbacks. Firstly, it's far too slow, taking about 3 secs to complete, which is a long time to wait between keystrokes. It'll only get worse as more records are added in future. Also, if multiple users are typing at once, they will both trigger the same looping script and alter the match field, which could cause some confusing results. So I'm more or less ready to conclude that what I'm trying to do is impossible, as it stands. Annoyingly, the actual functionality is fairly common in other places (both online and other programs etc.) so my users are half expecting that I should be able to make it happen by just ticking a box somewhere. Next step is to look for alternatives that provide the same "steering" towards the correct terms. Possibly some kind of pop-up or popover portal with suggestions?
  8. In typical fashion, I've blundered in with an attempt at a solution to this. I think I'm headed along the right lines, but have hit a bit of a roadblock. Here's how it looks: The user entering line items has a text field. (Called Item_description) I've got a script trigger set on keystroke. This script sets a global variable (called $$itementry) to be the same as item_description is at that point in time. Then, over in the Products table, there is a calculation field, based on PatternCount function, to see if each product record matches the $$ItemEntry variable. This returns "1" for any matches. I then use this calculation field as a match field for a relationship back to to the line items table, and use that to add a value list to the item_description field. That all seemed like it would make sense, but it isn't working. The script executes fine and I can see the variable being set in dataviewer. I suspect that the problem lies with the relationship back again, possibly down to the storage/indexing of the key fields?
  9. I'm revisiting this older post to share the solution I've found. Basically, the easiest way to duplicate all the related items is a looping script which works through each related item record in turn, duplicates it, and sets the link field appropriately. When I first asked the question I wasn't familiar with using variables, which is much more elegant than using global fields as I'd first imagined would be necessary. There is a far better description of the process here: https://www.filemaker.tv/uncategorized/filemaker-duplicate-invoice/
  10. I’ve been working on a database for a rental company, on and off, for quite a few years. Some of the answers to my previous queries here have been really useful in moving things along. The volume of orders has increased to the point where we need to track things more carefully. The crucial bit is spotting shortages/overbooking, but it would also be useful to generate proper reports on hire volumes for each item, rather than relying on anecdotal evidence. However, the actual "line items” on each order are just text fields that users can freely enter. We try and impose some discipline with consistent names for popular items, but there’s always a fair bit of deviation. (The difference between, say, “8ft ladder”, “Eight foot ladder”, and “Ladder - 8ft”) This starts to get messy when we are trying to conduct searches to check availability. What I’m hoping to implement is a list of autocomplete options that appear as users type in their line items. The autocomplete recommendations would ideally be sourced from a different table, which is essentially an inventory list. I can get a “live” autocomplete by using a drop-down list, and selecting “autocomplete using value list”, but this doesn’t seem to be selectable when I’m using a list based on a field in a related table. I also need to filter the list, based on what the user is typing. Unfortunately there are enough custom entries required that I can’t restrict user’s ability to enter any text. The autocomplete should hopefully be a nudge in the right direction, without being so restrictive that it frustrates them. I’ve had a good read through the many topics on Value Lists, and I’m still not quite sure if what I’m aiming for here is possible or not. I’m also wary of what sort of overhead it’ll introduce. Speed is critical, because users are often taking down orders with customers on the phone. If something’s convoluted enough to cause a noticeable slowdown, that’d be bad news.
  11. Sorry, I'm not sure I understand that. Does it mean that the more fields there are in a table, the slower it operates, even if those fields aren't being used for much? At the moment we have four questions/data points that we definitely want to look at. However I'm sure more will come up as the exercise proceeds, hence my estimate of 8-10. I can definitely appreciate points 3+4, as one of my jobs when revamping the main table a while ago was culling old fields that had been set up for a single-use purpose and abandoned thereafter. Some had helpfully descriptive names (like "Should we send them a 2004 Christmas mailshot?") but others were a bit more obscure. There is something quite attractive about a "bolt-on" solution that can be left out entirely when not required. Is it better to do this a separate file, rather than a new table within the same main file?
  12. Thanks for all the replies - that's a very helpful start. I agree that "variables" was a poor choice of terminology. Already I'm beginning to realise that I need to think things through in a bit more detail before jumping in and starting coding, which is good. From a business perspective, we have a few unrelated questions that we want to answer. I'm still fleshing out the exact questions, but I'm fairly sure that most of them will be simple "Yes/No" propositions. Things like "Would they be further away from us if we moved to x location?" My concern is that with half a dozen questions we're adding a lot of overhead to the file, for something that is going to be used very rarely. I'm not anticipating that we'd need to summarise by more than one of these new attributes, it'll more be a case of, taking the example above, looking at the total order value of people who would be further away if we moved. That's a very useful data point for informing a potential discussion. Reading through LaRetta's first post, I'm thinking that a separate table holding the question data for each order might be the neatest way to do it, since it avoids adding more fields to the main order table. Once the process is finished I could potentially bin the entire table and be more or less back to where we are now.
  13. I’ve got a bit of an analysis task coming up over the next few weeks. The idea is that we’re going to be sifting through a database of orders, and essentially tagging each order with assorted variables. At the moment what we have is a fairly basic invoicing system, there’s a table of orders and a related table of line items for each order. The plan is that we’d add field(s) to the Order table for the variables we want to track, and then it’ll be a process of manually reviewing each order. The variables have to be added manually, there’s nothing there that could be derived from a calculation or summary on other fields unfortunately. It’s things like whether the customer arrived by car, whether they booked by phone, that kind of thing. We’re relying largely on people’s recollections but since all I’m after is the broad brushstrokes perfect accuracy isn’t vital. One record could easily have multiple variables tagged. At the moment I’m trying to think of the best way to structure this. The seemingly obvious way to do it would be to add a field for each variable we want to track. Since they’re largely mutually exclusive I don’t think a single value list is the way to go. However adding 8-10 fields is going to be messy. Once we’re finished tagging orders I’ll be looking to generate some simple reports, in terms of order numbers and value for each variable. I suppose I’m just looking for advice as to the most efficient, least cumbersome way of structuring this. The less “footprint” this process has, the better, as it’s not a core function of the system. The other thing I need to take into account is the tagging process itself. Even clicking on a check box is going to take quite a bit of time - we have several hundred orders to get through and a few wasted seconds per order soon stacks up. The last time I did something similar I was able to build a stand-alone database for this particular purpose (sifting through mailing data to work out whether it was worth sending anything to each address). I ended up writing a simple script which set the field and moved onto the next record, then had them appear at the top of the menu so that Command-1 etc. would trigger them. This sped up the process quite a bit, but it would be tricky to implement something similar on a live database - users are already regularly using the shortcuts for other things. Any good tips for quick selection of each variable?
  14. Thanks, Don - that certainly seems to make sense to me. I might try duplicating the layout and removing conditional formatting bit by bit to see if there is any one piece that is choking the system. I'm thinking that I'll try a hardware upgrade first and see if the additional horsepower helps, before hiring someone in. Hopefully if I throw 8x the RAM and 4x the processing power at it things will speed up at least a little. At least if a new machine does nothing, it could be sold on again or has at least freed up a slightly faster client machine for use elsewhere. I've no real experience of Filemaker Server (short of mucking around with a demo of an older version many moons ago) but I'm guessing that it wouldn't magically handle these sorts of calculations any quicker than the peer-to-peer sharing that we're using just now? (At least, for the limited number of clients that I'm dealing with)
  15. Also I've just run the script debugger on the main opening script. No errors reported and only one "Call stack" is accessed. When I open the database for the first time, I get asked for my login almost immediately, but then there's a long pause before the layout appears. However nothing appears in the script debugger until the layout appears - after that it rattles through the script steps more or less instantly. I'm guessing that this indicates that the problem isn't with the script itself?
  • Create New...

Important Information

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