You are viewing limited content. For full access, please sign in.

Question

Question

Forms Calculations in a table that remain on their row and are not effected by fields in other rows

Forms
Updated February 9, 2024
asked on February 7, 2024 • Show version history

I have the following calculation in a table

=MULT(INDEX(PrimaryTable.Total_Unit_Cost,ROW()),INDEX(PrimaryTable.Billed_Qty_this_Period,ROW()))

It is the product of 2 other fields in the same row, however if I override the calculated value with my own input and then change the value of a field on another row in the table, it re-calculates all rows overriding my manually input value. I want to isolate the calculations to a specific row so that only changes made in that row trigger the calculation.

Is there a way to have a calculation that remains on it's row and is not effected by fields on other rows?

As a side question and possible workaround, can I use an IF statement before executing a Calculation. For example, if Index(Field1,ROW()) != Something, then use this calculation else don't calculate on this field and allow free entry.

0 0

Replies

replied on February 7, 2024

I don't think you can prevent the calculation the way you are hoping.  Even the IF statement idea probably won't work, because that is still a calculation and will try to run the calculation, removing what you manually entered.

Perhaps you could have two versions of the field, one that is calculated and one that is not, and some way to choose which to use, and hide the other.

0 0
replied on February 7, 2024

Thank you, this was my backup plan, but if we can have 1 field dedicated to this value it would be better.

0 0
replied on February 8, 2024

I created a second field that is just free entry with no calculation and told it to show this field and hide the original field for rows that it applies. However it shows both columns and looks terrible. It hides the field but does not replace the field with the new one.

0 0
replied on February 8, 2024

I wrote a JS method instead which just checks the value of another field before running the math, this way it is not fighting with fields users are editing directly on rows which allow this. Surprised you can't do this with the Calc language, the IF statement in Calc only allows writing another value instead, not exiting the logic.

0 0
replied on February 7, 2024

I am unable to reproduce this on cloud layout designer, are you on self-hosted? 

0 0
View 5 previous replies
replied on February 7, 2024 • Show version history

I am working in Cloud as well. Here is a simple reproduction summing the first column with the number 2, replacing the calculated value in row 1 and then changing the field in row 2 and having row 1 reset to the calculated value.

0 0
replied on February 7, 2024

Interesting, it looks like you are using the classic form designer. I was able to reproduce this. We had a similar issue come up in our testing and I am currently triaging if this has been resolved internally.

 

In the meantime, would this form support the new form designer? If so it will work there.

0 0
replied on February 8, 2024

We want to be able to write javascript for this form so the classic designer is best.

0 0
replied on February 8, 2024

I am the PM of the Forms product, I would be curious to learn more about the JS you need on the form that isn't supported on the layout form designer. We are actively trying to cover all the cases we can.

0 0
replied on February 8, 2024

I never know what I will need JS for so it is risky not to have it available for almost any form, but for this specific form we have a field rule that shows a loading screen in place of the table until all the data is loaded in. The field rule has no option to show/hide based on starting a lookup rule or when a lookup rule is finished so we use JS to detect this and populate a hidden trigger field for the field rule.

Before we did this, the form appeared unresponsive and even allowed users to try to work with the table while the lookup was running.

By hiding the table that is being populated and replacing it with a loading screen the form feels responsive, you can see that you initiated a lookup and see when it is ready for you to work with again.

Other uses of JS are:

  • Require draw signature
  • Making fields read only (we have stability issues with using the read-only option built into the field config)
  • Changing a field that contains a hyperlink to be clickable
  • Apply CSS classes to a field based on it's value
  • Functions like Split, ReplaceAll, etc
  • Calculating Date and Times
  • Setting which dates are selectable from a date field
  • Detecting current browser resolution so that accurate CSS classes can be assigned
  • Parse CSV files into a table
  • Math (When Calculations are not working) * I am actually going to do this now for this form as a workaround for this problem
  • Instantiating new objects like a small message or warning as a child object of a field, so that it shows exactly where it needs to be seen
  • Real-time REST API calls
0 0
replied on February 8, 2024

Love this extensive list, thank you! I do agree we don't currently support everything you need and needing to convert from the layout to classic designer is not supported. We do support many of the things you need though, I added comments to your list below! And if you don't mind I may reach out at some point in the future to get more feedback on how you are using forms.

  • Show/hide fields based on lookups
    • Supporting this in field rules is an excellent idea, I'll note this down
    • You can do this in JS with show/hide fields and the onLookupTrigger/Done
  • Require draw signature
    • Although you cannot conditionally require specific signature types, the signature field settings allow you to specify the allowed signature types
  • Making fields read only (we have stability issues with using the read-only option built into the field config)
    • The new form designer has a "disabled" state that is basically front-end read only which I think would fix your stability issues with backend validation
  • Changing a field that contains a hyperlink to be clickable
    • I currently do this with custom html, you would hide the field with the URL and have an adjacent custom html field create the link for you (can support on change/on load so it is dynamic)
  • Apply CSS classes to a field based on it's value
    • Can use the addCSSClasses function
  • Functions like Split, ReplaceAll, etc
    • You have full access to JS from the sandbox, depending on what you do with these functions all use cases I can think of would be supported
  • Calculating Date and Times
    • Same as above
  • Setting which dates are selectable from a date field
    • This is coming! We are super excited for this feature. We have one phase coming out next month for using today/tomorrow/relative dates for min/max and hopefully the following month functionality to specify dates between the min/max that are selectable (i.e., cannot select weekends)
  • Detecting current browser resolution so that accurate CSS classes can be assigned
    • The best way to do this would be with bootstrap classes and media queries for both designers
    • Bootstrap v4 for the layout designer
    • Bootstrap v3 for the classic designer
  • Parse CSV files into a table
    • Not sure where the CSV file is coming from, if it is coming from a file upload on the form we do not currently support retrieving this content, but this could be a feature we could add
  • Math (When Calculations are not working) * I am actually going to do this now for this form as a workaround for this problem
    • Same as above, all cases I can think of should still work between get/set field values
  • Instantiating new objects like a small message or warning as a child object of a field, so that it shows exactly where it needs to be seen
    • You cannot inject custom html, but I have used text below field and some CSS styling to do this on the layout designer
  • Real-time REST API calls
    • You have full access to JS from the sandbox so you can do this. Just depends on what you want to do with this data
    • We are adding support to Web Requests as a lookup source next month as well!
0 0
replied on February 8, 2024

The HTML for hyperlinks is a good alternative.

Will look into the bootstrap CSS features next time I have a wide form.

Not sure how the method addCSSClasses would work in the new designer on it's own. We need to know when to remove and add classes based on the current value. For example say I want everything over 90% to have the class .blue and have any other color class removed.

With JQuery we have .each methods and we can access each field's value and attributes using $(this) in the loop. With the modern designer it seems you can not modify anything in a .each loop, it is just cycling through a copy of the values or something, not the actual live field.

Looking forward to a simpler way to restrict dates in the calendar and web requests in lookup rules sounds interesting. The reply is usually in JSON and needs to have the values extracted somehow, interested in seeing this.

We read CSV files from an upload, this allows someone who uses online Sheets applications to quickly get table data into a form. You can do it with Workflow and move on to a new user task, but with JS it is instant. We even use it in-house to read the CSV file provided by order desk on a new quote.

0 0
replied on February 8, 2024 • Show version history

Ya there is a different mindset you need to have when it comes to coding with the new form designer.

For CSS Classes, I would group the fields you want to "watch" a certain way, for example using a base class on that field, and then on change you can add the classes. For example:
 

const watchFields = LFForm.findFieldsByClassName('watch-field');

const addClassIfLargeValue = async function (fieldId) {
  const value = LFForm.getFieldValues({ fieldId });
  if (value >= 90) {
    await LFForm.addCSSClasses({ fieldId }, 'blue');
  } else {
    await LFForm.removeCSSClasses({ fieldId }, 'blue');
  }
};
const main = async () => {
  for (const field of watchFields) {
    await addClassIfLargeValue(field.fieldId);
    LFForm.onFieldChange((e) => addClassIfLargeValue(e.fieldId), {
      fieldId: field.fieldId,
    });
  }
};
main();

 

Will explore more about the csv option. thats a cool feature!

0 0
You are not allowed to follow up in this post.

Sign in to reply to this post.

Details
Related Posts
Loading ...