Pre-populated values from previous record

Ask for Help
1

Hello,

I’m trying to create an handrail app for tracking ambulance usage (one app = one vehicule, no conflict).
When creating a new record, I would like to pre-populate some fields with previous record ones, such

  • kilometers
  • general state comment
  • pictures of possible damages (picture picker)

eg. when creating a new record, re-use C5 value as default value (4000)
image

and of course be able to override the value.

I wasn’t able to find a way to achieve it so far, I’ve tried some google sheet script with onEdit(), but I suspect a new row isn’t yet triggered on glide “add item” action.

function onEdit(e) {
  var sh = SpreadsheetApp.getActiveSheet();
    var row = sh.getActiveRange().getRow();
  
  if(sh.getRange(row,3).isBlank()){
    sh.getRange(row,3).setValue(sh.getRange(row-1,3).getValue());
  }
}

any ideas welcome :slight_smile:

2

Hi camous,

When you say pre-populate, I assume you mean filling them by the previous record?

How are you “creating” a new record? By filling a form? Can you share with us a bit more about your flow?

3

My bad for missing details, still new to glide (beside dev background).

Yes, your assumption is correct, filling fields from previous records, and then be able to override them if necessary.

below my “add” layout, highlighted in blue “start kilometer” (sorry for the french screenshot)

image
image379×624 20.7 KB

with value in C5

4

So the flow is:

  • Adding a new item via the form, with the option of not filling the “start kilometer” part.
  • If it is not populated after the form submission, the script will take care of that and fill the value above to the new cell.

Is it correct? I can help you with that.

5

Not exactly

  1. create a new item via the form
  2. field “start kilometer” (field required) filled by previous record value. in my example 4000
  3. user can update or let default value
  4. save item
6

In case of pre-populated value inside the form, at the moment it’s not possible, as far as I know.

8

ended up with an hybrid solution :slight_smile:

my flow accepts having a 2 steps data entry (add & then edit): first add with generic data (none pre-populated) and the edit one with prepopulated data, and in between trigger a google sheet script.

  1. add generic info (+ small text saying that some extra info need to be added after)
  2. glide triggers google sheet onEvent method (beware, onChange seems not triggered) making the work
  3. edit item, with data pre-populated in #2.

image
image756×101 6.79 KB

google sheet script has to be optimized and reduce amount of call (if you have several field to prepopulate), otherwise glide will re-sync on first of them and then miss the extra ones.

below my script, ids array contains the columns to be replicated

function onEvent(e) {
  var ids = [3, 7,  8, 9, 10, 11 ,12].sort(function(a, b) {return a - b;}); // indexes column to replicate from previous row if current value is null, and sort.

  var sh = SpreadsheetApp.getActiveSheet();
  var row = sh.getActiveRange().getRow();
  
  if(row == 1){
    return;
  }
        
  // as we prefer less calls to spreedsheet, we get min/max for narrowing down data retrieved & updated
  var min = ids[0];
  var max = ids[ids.length-1]; 
  
  // get values from previous line
  var previousLineValues = sh.getRange(row-1, min, row-1, max).getValues();
  
  // get range (for future setValues) and values for current lines
  var currentLineRange = sh.getRange(row,min, row, max);
  var currentLineValues = currentLineRange.getValues();
  
  for(var i=0;i<ids.length;i++)
  {
    var offset = ids[i] - min; // id of the column shifted as we only got range starting with min
    
    if(currentLineValues[0][offset] == ""){
      currentLineValues[0][offset] = previousLineValues[0][offset]; // if empty (isBlank() only on range), we override
    }
  }
  
  currentLineRange.setValues(currentLineValues); // we update the whole set of values at once, preventing partial retrieval from glide
}

open if better approach preventing me having my entry in 2 screens (add + edit) which could lead to required fields missing.

9

Nice to see that you found the solution!

Regarding this, it’s strange, because usually onChange works, onEdit is the one that does not.

10

my bad, I mixed up, it’s indeed onChange working (as displayed on the screenshot) rather on onEdit. you’re correct.

1 Like