Jump to content


  • Content Count

  • Joined

  • Last visited

  • Days Won


benmort81 last won the day on July 6 2018

benmort81 had the most liked content!

Community Reputation

2 Neutral

About benmort81

  • Rank

Profile Information

  • Gender
    Not Telling

Recent Profile Visitors

1,616 profile views
  1. Hi Diver, Manipulating the ~list into a format that works in sql, and then constructing the ~sql query string with the formatted list sandwiched, like ...IN (" & ~here & ")" worked for me: Let ([ ~List = TableA::Summary_List ~SubList = Substitute ( ~List; "¶" ; "','"); ~here = "'" & ~SubList & "'"; ~sql = "SELECT Field FROM TableB WHERE Field = ? AND FK_ID IN (" & ~here & ")"; ]; ExecuteSQL ( ~sql; ""; ""; 1) )
  2. Thanks for sharing these sub-scripts. Using Windows OS, I still have the problem that the user's current screen is resized, albeit briefly whilst the new process window comes to the fore (off-screen). I have worked around this by reducing the user's window by 4 points on layout enter. It's not perfect, but it does not interrupt the work flow or lead to ugly jumping around, so that will have to do for now. Thanks again. Ben
  3. Thanks all for the advice - I'll experiment with the techniques you have mentioned.
  4. Hello, I am using Windows OS for my solution. I have a layout in list view showing events. An admin user is to add groups of people as attendees of these events by pressing a button to run a script. The script Freezes the window, changes layout, adds the attendees in the relevant table and returns the user to the original layout. This all works well, except for when the script returns to the original layout and unfreezes the window, the scroll position of the active row (the record that the user is editing) jumps to the bottom of the screen. I would like the scroll position to remain static or be returned to where it was before the script changes the layout - so a change is imperceptible to the user. The Window Scroll options seem limited to top, bottom, pg up, pg down. I have also tried having the script open a new window before changing the layout, but you then see the script working - if only briefly - as the new window comes to the front. Has anybody got any suggestions on how I can return the scroll position to exactly where it was before the script executes? Thanks in advance, Ben
  5. Thank you. My question is: "What is the correct script parameter for the following..." GetFieldName ( TheFieldIJustClicked ) Thanks again, Ben
  6. Hello, In my table I have text fields "ABC", "DEF", "GHI". I have these fields on a layout and have set each field as a button that runs a script. The script needs to return the field name (GetFieldName?) and change the contents of the field based on the existing contents of the field when the button is clicked (Case function). How do I let my script know which button (field) has been pressed? Thanks in advance, Ben
  7. Genius! This is neat! I had worked around it by sorting the relationship in descending date order and then adding a calculated field Last_Ob_Score to the Staff table which = observation score from the Observation table - then doing a simple ExecuteSQL from the Last_Ob_Score field. No need for the extra database overhead with your query. Thanks
  8. Hello All, I have a table "Staff" and a related table "Lesson_Observations". A Staff record has many Lesson_Observation records. Each lesson observation is attributed a score. I have a working ExecuteSQL calculation that finds the average of all lesson observations scores for staff in a specific Faculty ("English" in the example below) and only where the Staff record has the attribute "Active". Let ( [ $query = "SELECT AVG(L.obCritAverageVal) FROM Lesson_Observation L INNER JOIN Staff S ON L.FK_StaffCode = S.PK_StaffCode WHERE S.Fk_FacName = ? AND S.Status = ?" ; $result = ExecuteSQL ( $query ; "" ; "" ; "English"; "Active") ]; $result )Each Lesson_Observation record has an attribute "Observation_Date". Is there a way I can modify the query above to find the average of only the MOST RECENT lesson_observation for each member of staff in a Faculty where that member is staff is still Active? Thank you! Ben
  9. Hello, Earlier in this post I was shown how to create context TOs to be able to filter on a attribute within a table (It works great, thanks EOS). In my solution the layout is CPD_FilterContext and the portal shows related records from CPD_Attendance, you can see this in the Relationship screenshot attached. I added another portal on the same layout (CPD_FilterContext) showing related rows from CPD (Same Table, different TO). This portal shows me all the CPD records as I require. I wanted to be able to add new CPD events straight from this portal so went and edited the relationship between 'CPD Attendance' and 'CPD' so that I could create records in CPD via this relationship. In the portal I get the blank row ready for new record entry - but when I try to enter data into any of the fields in the portal I get the error: "This operation cannot be performed because one or more required related records are not available and cannot be created." I can script something so the end-user can add and edit CPD events easily from this layout - but it would be simpler if they could do it straight in the portal. I'm not 'getting' this level of complexity yet with the relationships, but I'm learning! Is this a problem with the relationship structure? Thanks you for any advice!
  10. Hello All, I want to script a find based on two fields. One field is based on a global field and is used 'straight' - I mean the global field's value is "Wider Life" and I want to search for all records with the value "Wider Life" I have another global field which is user selected {"Awaiting Confirmation"; "Confirmed"; "Rejected"}. I need to convert this to a single letter {"A"; "C"; "R"} using a Case in a calculated result of a set field like this: Go to Layout [ “CPD Attendance” (CPD Attendance) ] Enter Find Mode [ ] Set Field [ CPD::CPD Type ; CPD::gTypeFilter ] Set Field [ CPD Attendance::CPD Confirmed ; Let ( [ $Txt = CPD Attendance::gConfirmFilter; $Sym = Case ( $Txt="Confirmed"; "C"; $Txt="Rejected"; "R"; $Txt="Awaiting Confirmation"; "A"; "@")]; CPD Attendance::CPD Confirmed=$Sym) ] Show Custom Dialog [ Title: "Find this in CPD_CONFIRMED field"; Message: $Sym; Default Button: “OK”, Commit: “Yes” ] Perform Find [ ]In the script debugger I can see that my temp variable $Sym has the value "A" as expected and the debugging custom dialogue returns "A" but the value "0" (Zero) is entered in the field for the find. The field type is set as Text. Any ideas what the problem is? Thanks very much! Ben
  11. Just changing the UI to checkbox works to show multiple types in the portal - I haven't had to do anything with I'm I missing something?
  12. Thanks Don. Absolutely no need for any apologies - I'm gob smacked by the level of help and support guys like you and Mr Eos gift on people like me! You have a great website btw, loads for me to get my teeth into there. Ok, good advice. Learning so much here, I'd better give the old brain some time to cool down!
  13. Works beautifully. Is this an extension of the first approach you suggested, or is this a 3-way join? How do you sort on a field from a related table in a portal as in your example file?
  • Create New...

Important Information

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