Question

ROW() Not Working in Collection Populated by Lookup

Forms

Updated May 31, 2019

asked on May 30, 2019

If I have a field in a collection with the formula =ROW() and I'm manually adding the row, this seems to work just fine.

But if the rows are being added via a lookup, with the "as new sets" option, the formulas don't seem to trigger to populate the fields.

I can get some other formulas that reference ROW() to work, such as =INDEX(collection.field, ROW()), but I can't get it to work in any way that seems to return the actual row number for the collection.

Has anyone else encountered this issue and know a way around it?

0 0

Replies

replied on May 31, 2019

Okay - I've figured out a workaround - but it's ugly, so I'm not marking this question as resolved in case anyone else has a better idea...

Realizing that this does not work:
=ROW()

And other similar things like these do not work either:
=ROW()*1 =ROW()+0 =ABS(ROW()) etc.

But this does work:
=INDEX(collection.field, ROW())

I did this:
=(INDEX(collection.field, ROW())*0)+ROW()

The first part gets cleared since it's multiplying by zero - it's only there because it triggers the formula to process. Since the formula has now processed, it'll work with the +ROW() at the end, where it wouldn't have worked with ROW() by itself, and my result is that I get the row number to work in the collection, with the lookups added including the "as new sets" setting.

1 0

replied on May 31, 2019 • Show version history

I had to do something with ROW() a while back. I nested it within a IF statement. Not sure if this gives yo unay ideas:

=IF(ROW()=1,

IF(SUM(INDEX(mealsTable.mealsQuant,1),1)=1, 0, PRODUCT(INDEX(mealsTable.mealsQuant,1),INDEX(mealsTable.mealsAmt,1))),

IF(ROW()=2, 

IF(SUM(INDEX(mealsTable.mealsQuant,2),1)=1, 0, 
PRODUCT(INDEX(mealsTable.mealsQuant,2),INDEX(mealsTable.mealsAmt,2))),

IF(ROW()=3, 

IF(SUM(INDEX(mealsTable.mealsQuant,3),1)=1, 0, PRODUCT(INDEX(mealsTable.mealsQuant,3),INDEX(mealsTable.mealsAmt,3))),

0)))

0 0

replied on May 31, 2019 • Show version history

If you need to retrigger the lookups source field, you can use javaScript to trigger a .change() on

'collection.field' (I am just guessing which field would change here...) to the source field. I have had some trouble with lookups not firing off correctly in the past. Not sure if that will do the trick for you.

$('.yourField input').on('change', function(){
      //this will retrigger the lookup because the field it looks to has changed
      $('.yourLookupSourceField input').change();
});

0 0

replied on May 31, 2019 • Show version history

Thanks @████████, but unfortunately this doesn't really work for this situation. There isn't actually a field triggering the lookup, the lookup doesn't have conditions, just fill settings, so it populates as soon as the form loads. And the lookup is working great, it's just the built-in formula functionality to get the row number that isn't working. Redoing the lookup wouldn't solve that problem.

And it does work with other formulas like =INDEX(collection.field, ROW()) it is just with =ROW() by itself that it isn't populating.

Even if it was working, I wouldn't be thrilled with a solution resorting to Javascript. I'm not afraid of Javascript by any means (I have extremely complex Javascripts on many of my forms). I just don't like the idea of needing to use Javascript in order to make the built-in formula functionality work properly.

0 0

You are not allowed to follow up in this post.

Question

Question

ROW() Not Working in Collection Populated by Lookup

Replies

Sign in to reply to this post.