Is there a way to have a database field calculate from other fields?

Need help
1

is there a way to have a database field be calculated from other database fields? I’ve tried to come up with a workflow that updates the field but it is a bit buggy and can’t get it to work right.

2 Likes
2

Yes, should be.

If you could put what you are doing in the forum app, then it might be easier to help ?

1 Like
3

I looked at adding a new page to the forum app, but I think it might be easier to explain it here since I actually don’t want the calculations done on the page, but would rather have then done in the database.

I have a Data Type “Keyword”, which has the following fields:

  • RelatedIdea: which is of type Data Type = “Idea”
  • CompetitionLevel: Data Type = text
  • CompetitionNumber: Data Type = number
  • KeyScore: Data Type = number
  • Volume: Data Type = number

I have a Data Type “Idea”, which has the following fields:

  • Keywords: Data Type = a list of “Keywords”
  • IdeaScore: Data Type = number

I have setup workflows to fill in some of these fields based on the inputs of other fields. My challenge is if a user edits those initial input fields later, I can’t seem to create a workflow that will recalculate the inputs. Also, it seems like it will be hard to maintain my app if I have database field logic embedded on specific pages instead of centralized in the database.

For example:
CompetitionLevel is input by the user via a dropdown box as High, Medium, or Low. I want to automatically convert those text values into either 1, 2, or 3, respectively and store it in CompetitionNumber field. I am doing this now with a workflow and it works for the users initial input, but if they edit their initial input CompetitionNumber does not update (and I can’t seem to get a workflow that will do it).

Example #2:
KeyScore is calculated via a workflow that takes the user’s inputs for CompetitionLevel, which I convert to CompetitionNumber, multiplied by the User’s input for Volume field.
I want to sum up the KeyScore field for the list of Keywords with the same RelatedIdea field and store it in IdeaScore.

I realize that is a long explanation, so please let me know if it is not clear. Any help would be greatly appreciated.

Thanks!

4

If you can get the data structure in the forum app that would certainly help.

I would certainly be tempted to concatenate CompetitionLevel/CompetitionNumber into its own data type. Then you don’t need to worry about converting the values.

5

Thanks NigelG. I added the data model to the forum app. As for concatenating CompetitionLevel/CompetitionNumber, would I still be able to run calculations on it? For example, I need to multiply it. If I concatenate, why would I not need to convert the values?

6

Hi

Were you able to take a look at the data model to see if you how it is possible to do calculations In the database directly.

7

Sorry, this fell of my radar. Will have a look.

8

So I think what you are asking is …

“Do I have to redo all those calculations across multiple tables each time I update something” ?

The answer is probably “yes”. There is no way to have the database automatically update itself, or at least that I know of.

What you can do is have a one custom workflow that rejigs everything. And you could run this “in the background” so to speak.

So you wouldn’t need to do it on every page. You could have a single “function”.

You could schedule this to run at some point in the future.

9

Thanks Nigel. Not what I was hoping for but I’ll try that.

10

You could do this by holding your data in a Google Sheet perhaps. But I think that just presents a whole new set of issues.

11

while giving condition i have to give ‘Is’nt logged in’ but i m not finding that fields… what i have to do for that?

12

I have this same question!

I originally built my application in Google sheets and then in Google AppSheet. These apps make it quite simple to calculate fields based on data in other fields, and my data structures relies on that.