Jump to content


  • Content Count

  • Joined

  • Last visited

Community Reputation

0 Neutral

About mrmacmusic

  • Rank

Recent Profile Visitors

1,965 profile views
  1. I would really appreciate some input on this, as I think I've got lost along the way trying to figure this out and whilst I have a working solution it seems somewhat convoluted (and is a bit sluggish to update)... I needed to create a report from the context of contacts so that this can display sub-summary data (sorted by contact::area and contact::category) of related survey data for each contact from the single most recent survey record (within a specified report date range) as well as count how many survey events took place in that date range. Because of the context, I concluded – perhaps wrongly – that I couldn't simply put the required summary fields in the surveys table and instead needed to pull the appropriate survey data for each contact into the parent record, then add the summary fields in that table. To do this I've added a bunch of un-stored calculation fields, namely: 1) wasOpen – since I'm working with all contacts records, this evaluates whether the contact was added after the reportEnd date or closed before the reportStart data and should therefore be included (1) or excluded (0) 2) mostRecentSurvey – calculated using ExecuteSQL( "SELECT ''||surveyDate FROM data JOIN contacts ON fk_contactID = contact_ID WHERE fk_contactID=? AND surveyDate<=? ORDER by surveyDate DESC FETCH FIRST ROW ONLY" ; "¶" ; "" ; contacts::contact_ID ; globals::reportEnd ) and used to create a relationship to a single survey record – contactID = fk_contactID AND mostRecentSurvey = surveys::surveyDate 3) survey_Q1 – answer to first question in related survey multiplied by the value of wasOpen so will only have a value if the contact is being included in the report 4) surveys_carried_out – calculated using ExecuteSQL( "SELECT COUNT surveyDate FROM data JOIN contacts ON fk_contactID = contact_ID WHERE fk_contactID=? AND surveyDate>=? AND surveyDate<=?" ; "¶" ; "" ; contacts::contact_ID ; globals::reportStart ; globals::reportEnd ) as this needs a figure for each contact whether it is included or excluded from the report. 5) summary field, total of survey_Q1 6) summary field, total of surveys_carried_out etc (There are obviously other survey questions/dates being pulled in to the parent and summarised in the same way) So when I set the global reportStart/reportEnd dates, goto the report layout, find all contacts and sort by contacts::area and contacts::category I get the required sub-summarised results, but I just feel I'm missing something and there is perhaps a better, more efficient approach... Thoughts?
  2. I'd appreciate any pointers with this... Essentially I have a working report layout setup with two sub-summary parts. These parts both have the same summary fields on them but the parts are setup to summarise when sorted by different sorts: one by contactArea, one by contactCategory. This works perfectly – when I sort by both contactArea and contactCategory at the same time, I get both overall Area totals and Category subtotals (there is also a Grand Summary part which provides overall totals, i.e. sum of all Areas). I can quite happily export this report as a PDF and that's fine, however I want to be able to capture all the summarised data weekly into records in a new reports table for future reference and analysis within the solution, but I'm not sure of the best way to go about this... I want to set the data fields in this new report table so they are static and not calculated on the fly and am presuming I could/should use the GetSummary function to grab the data, but how does this work (or does it work at all) when I have 2 break fields (contactArea and contactCategory)..? I suppose I could just find records based on area and category first – i.e. show all north, category1 – then calculate the summaries I need but is there a better way..?
  3. ​...tried that Kris (or rather tried a hard reset instructing him to hold down the power/home button until the Apple logo appears) and it makes no difference – following the iPhone reboot it still won't let him swipe away the Filemaker Go app... Puzzled, and more than a little concerned.
  4. ​Thanks Wim, I'll give that a bash... (No, I don't leave the Admin Console open for long periods)
  5. I've never come across this before... one of our mobile users has reported that Filemaker Go can't be swiped up to force close it – it just keeps bouncing back down Any ideas?!?
  6. I've noticed Server Admin Console (v13.0.9.905) is showing "0 Clients are currently connected" when in fact several are... Also in the Schedules section it reports that the last backups were performed on the 20th May and are next due to run on the 21st May however they've been running every day up to and including last night... I'm a bit concerned about this... any ideas?!?
  7. Thanks for the clarification and advice comment – I figured that might be the case and I'd be best just creating the required relationships.
  8. Hopefully I've posted this in the right place and someone can point me in the right direction here... I have staff and staffAllocations tables related by staffID. For each staff member I'm trying to visualise their events from the allocations table, displaying them in a row so that the user can easily see what events are allocated to them over the next 10 days. I'm using a repeating field (10 repetitions ) in the staff table with the following calculation: Let ( [ current_date = Extend ( gview_startDate ) + Get ( CalculationRepetitionNumber ) ] ; If ( current_date > Extend ( staffAllocations::dateStart ) and current_date < Extend ( staffAllocations::dateEnd ) +2 ; Extend ( staffAllocations::_eventID) ) ) ...which works apart from the fact that it only shows one (the first) related record. It does pull the eventID into the appropriate repetition(s) depending on date range for the event but I can't work out why that calc wouldn't evaluated on all repetitions and therefore show all appropriate events... I'm trying to avoid using lots of portals and relationships to generate my summarised view of events so hope I'm missing something simple (or even something relatively complex..) Any help or advice with this would be greatly appreciated!
  9. I'm having the same problem and wondered if other users of FM EasySync can shed light on how long (roughly) it actually takes to process a single record..? My solution is taking around 3-4 seconds per record...
  10. Thanks for that eos LOL! Ah yes, FilterValues... I knew it was in there somewhere! Part of the problem with having to dip in and out of FMPro development rather than doing it full time is that my brain can take a bit of time to get back up to speed... Thanks for the heads up on the more straightforward related table option... makes sense.
  11. I've hit a snag using PatternCount to count values within a list for a summary chart and hope someone can point me in the direction of an alternative solution... Here's my problem: I have a summaryReferralReason Summary field which is a List of the referralReasons field, which can contain 5 different unique values selected from a drop-down list and are the subject for a simple bar chart in my summary layout. I couldn't understand why adding up the totals for each of these 5 values in my bar chart were different from the summarised total (which is simply a count of all values) and then it twigged... d'oh!! Here are the first three lines of the calculation I've used to generate the chart data... PatternCount ( contacts::summaryReferralReason ; "Other" ) & ¶ & PatternCount ( contacts::summaryReferralReason ; "Another Retailer" ) & ¶ & PatternCount ( contacts::summaryReferralReason ; "Trade Press" )... ...and I've realised that my problem is that PatternCount will count every instance of 'Another Retailer' in the Summary List because 'another' contains the characters 'other' Am I missing a straightforward fix that will count exact text strings or will this require some lateral thinking? Everything else in the summary layout is working a treat so any help or advice with this would be much appreciated!
  12. Whilst syncing appears to have been working well within the solution I built, I have a situation whereby a number of records weren't pushed to the host from the remote client: Changes made on day 1 - sync appeared to run OK, host data updated Changes made on day 2 - sync appeared to run OK, data on host not updated Changes made on day 3 - sync appeared to run OK, host data updated I have no idea why the sync on day 2 would appear to have worked fine yet on later inspection of the data it transpires that record updates were not actually pushed to the host... I realise the remote client can run the 'reset' script and then sync but am I not right in thinking that this would update all records on the mobile device replacing with the data from the host (i.e. losing changes made on the remote device)..? This would be far from ideal. Can I run a similar reset script on the host – potentially only on select records – to force these to be updated the next time the remote device syncs (push to server)..? I suggested to the client that she "manipulate" the data on the device in those records which failed to sync on day 2 so that they are then pushed to the host the next time she syncs which hopefully will solve the problem for now, but I wonder if there is a better way...? Any help or advice would be appreciated!
  • Create New...

Important Information

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