0

Using a table selection in script

Hi,

I'd like to be able to scroll through a set of records in a table and update values on each record.  I want to select the records using the selection feature on table view, as used for bulk update, delete etc. Is that possible?

13 replies

null
    • Fred
    • 8 mths ago
    • Reported - view

    Hi Chris -

    Most things are possible in Ninox.

     said:
    I'd like to be able to scroll through a set of records in a table and update values on each record.

    I'm not sure what you want to do here? You can already do this in the table view. You can double click on a column and update the data.

     said:
    I want to select the records using the selection feature on table view, as used for bulk update, delete etc. Is that possible?

    Can you add some more description of what you want to do? Maybe describe a work flow that you want to do.

    • Chris_Mullett
    • 8 mths ago
    • Reported - view

    Thanks Fred, should have been more descriptive. Basically I'm trying to bulk update records which I manually select and which are not necessarily selectable by query.  I can't do it using the bulk update feature as the fields to update are linked to other tables. I will create a form with links to the relevant tables and select the values to update to, then scroll  through the selected records updating using linked record IDs. I could use bulk update/assign calculated values, but that would mean looking up the ID's and entering the formula for each of the four fields every time I want to do it.

    I am using the DB to track the films I shoot (I'm into analogue photography!) so record camera, lens, film, location etc. I batch develop the films, so need to add fields for chemistry, time, temperature, agitation which are all selected from linked tables. Currently I add these details individually, but a bulk operation would be a useful embellishment. 

    • Fred
    • 8 mths ago
    • Reported - view
     said:
    Basically I'm trying to bulk update records which I manually select and which are not necessarily selectable by query.

    May I recommend that you look into using a dynamic multiple choice field. This will allow you to select the records you want to update.

    You can see it in action with the attached DB. In the Dashboard table you can select a Customer then it will bring up the Outstanding Invoices. You then enter an amount into Amount Recieved, make your selection of invoices then click on Pay Selected and it will do its magic.

    fredv2.ninox
      • Chris_Mullett
      • 8 mths ago
      • Reported - view

       Thanks Fred. This looks very interesting. I'll spend sometime looking at this and seeing how to use the techniques to solve my issue.

      • Chris_Mullett
      • 8 mths ago
      • Reported - view

       I've spent some time exploring the use of Dynamic Choice fields and I thin k it is the way to go. I've looked at your db and it almost makes sense to me although it doesn't seem to work properly as no values seem to update - probably operator error :-)
      I've made progress by building a dashboard with a combobox linked to a  table Chemistry which I use as the Dynamic Value for a Dynamic Multiple Choice in table Film and it work as expected provided the combobox value is not empty. In this case the DMC is also empty and I want it to show records where the Chemistry field is  also empty .
      I have tried adding code to the Dynamic Value box:

       if 'Select Chemistry'.Film != null then
          'Select Chemistry'.Film
            else
             "" end

      but still get an empty DMC field.

      • Fred
      • 8 mths ago
      • Reported - view

      A bit of background. Dynamic fields are built on records of a table. Each record is a choice.

      So with that in mind, I see that you want to use a reference field 'Select Chemistry' to build this dynamic field. Smart. Always use reference fields when ever possible.

      The next question is if Film is a reference field or not? If not then Ninox can not build the dynamic field.

      Since you are using a reference field you can simplify your code to:

      'Select Chemistry'

      Then you can put Film into Dynamic Value Name.

      No need to check if 'Select Chemistry' has any records. If it does then it will show the related records. If it doesn't then it won't show anything. You could put some code in the Display if... code so it only shows if there are related records.

      • Chris_Mullett
      • 8 mths ago
      • Reported - view

       Thanks Fred.

      There are two tables, Chemistry (containing a few details of the chemistry used for development) and Film, which contains info about the  films I used, including a reference field  to the Chemistry table which records the chemistry used for each film. I often batch develop upto six films in the same batch of chemical. As things stand I have to enter the chemistry details on each film so I'd like the dynamic multiple to list the Film records where the Chemistry field is empty. 

      I know 'Select Chemistry' will list Film records where the Film.Chemistry = Select Chemistry, but only if Film.Chemistry is not empty. My code was a failed attempt to force it to list Film records with empty Chemistry field when Select Chemistry is also empty. Does that make more sense? I hope so. Thanks for your patience.

      • Fred
      • 8 mths ago
      • Reported - view

      With relationship fields you need to know if it is a 1:N or N:1 (one to many or many to one). If you only see a popup field then you are on the many side of the relationship. If you see a table then you are on the 1 side. Ninox also shows you when you look at the relationship field.

      this shows up as a table

      vs

      this shows only one record

      Can you upload a copy of your DB without any personal data? Or add me as an admin to a test DB in the cloud?

    • Chris_Mullett
    • 8 mths ago
    • Reported - view

    Here is a link to a copy: https://www.dropbox.com/scl/fi/q9st7plthznmjmb116y7x/Copy_for-Fred.ninox?rlkey=pibbfqz583gf2ej9efii6ev6y&dl=0.
    It will open the Maintenance Dashboard and I've added couple of explanatory notes.

    • Fred
    • 8 mths ago
    • Reported - view

    You wrote in the DB:  I would like them to include records where Film.Chemistry id empty when  ‘Select Chemistry’ is empty.

    Just to verify, you want to show records in Film where Film.Chemistry = 'Select Chemistry' and Film.Chemistry = null?

    or

    Do you want to show records in Film where Film.Chemistry = null when 'Select Chemistry' is null?

    You can also attach files to post. Just click on the paperclip icon above the Save/Cancel buttons.

      • Chris_Mullett
      • 8 mths ago
      • Reported - view

       Sorry typo in last post, should be ...Film.Chemistry IS empty ...
      I am struggling to see the difference between your two scenarios, but I think the later is a clearer description .

      It occurred to me overnight that I could add an empty record to Chemistry, which would add another row to Select Chemistry  and picking that should show rows in DMC where Chemistry is empty. It failed.

      The Select Chemistry drop down gives the option to select (empty). If this is selected I want the DMC object to list Records from Film where the Chemistry field is empty.  

      The only  way I can get it to work is to add a default value eg MT to the Film.Chemistry field and a corresponding entry in the Chemistry table. Not a very elegant solution but it works.

    • Fred
    • 8 mths ago
    • Reported - view
     said:
    I am struggling to see the difference between your two scenarios, but I think the later is a clearer description

    Put another way, the first scenario asks if you want to include Film records that equal the selection (when Chemistry = 'Select Chemistry') and when Chemistry = null?

    The second scenario ask if you only want records where Chemistry is null.

    Try this in the Dynamic Value:

    let t := this;
if 'Select Chemistry'.Film != null then
    select Film where Chemistry = t.'Select Chemistry'
else
    select Film where Chemistry = null
end

    Line 1, uses the the this command to get the record Id (rid) of the current record. Which then allows you to then access any field of the current table later in your code. You see this in line 3.

    Lines 2 - 6 is the if then statement. While you used the reference field of 'Select Chemistry', we have to switch to the select statement because we need to access the entire table if we want to find all records where Chemistry = null.

      • Chris_Mullett
      • 8 mths ago
      • Reported - view

       That is exactly what I was looking for and I now understand the differences between the two scenarios. I really do appreciate your help with this.

      Regards

      Chris

Login to reply

Content aside

  • Status Answered
  • 8 mths agoLast active
  • 13Replies
  • 98Views
  • 1 Following