Columns vs Formulas - Best Practice

[Smitster]

Morning all,

Question i’m hoping you can help me with.

So we currently have a large input form with table columns as follows:

Op1Input
Op1InputDate
Op1InputText
Op2Input
Op2InputDate
Op2InputText

etc

The idea is that the user enters input data into the “Op1Input” column and this is timestamped in the “Op1InputDate” column.

As soon as data is entered into the “Op1Input” column, the column is hidden and replaced with the “Op1InputText” column, which is a Show - Text column type which displays text to the user along the lines of “Text [Op1Input], Timestamp [Op1InputDate]”

All is set up and working as expected.

We are currently looking at ways to optimise the app to reduce sync times, and were thinking we could potentially replace “Op1InputText”, “Op2InputText” and so on with just one column, “OpInputText”. This would then contain a formula along the lines of:

If [Op1Input] is not blank, show text for op1
or
if [Op1Input] is not blank and [Op2Input] is not blank, show text for op1 + op2

etc

We have a large number of input fields for the above, anywhere from 30-50 operations, so the above would obviously reduce the number of columns in the app quite considerably. It would however create a pretty large formula for the [OpInputText] column.

I was wondering what users thought was best practice to reduce sync times, have one column with a large formula or multiple columns with much simpler formulas?

Or would both methods be pretty much equivalent?

Thanks

[Aleksi · Accepted solution]

If you are using a Show/Text type with the normal column, you can combine them together because that formula is not recalculated when you sync the app. It will recalculated only when you open and save the record.

[Smitster]

Thank you.

[Grant_Stead]

What are you trying to do, why would you have 50 renditions of that information across columns of a table. With a little more insight we might could help a bit more.

[Smitster]

We have a Shop Floor Traveler where operators confirm a operation has been completed by entering a unique pin code.

Travelers can range from 1-2 operations up to 50.

The above makes up a continuous form where the operator is asked to enter their pin when an operation is complete, this is timelogged and then the text is displayed on the form to show which operator has completed that operation and when.

[Grant_Stead]

Gotcha, I haven’t worked production on a floor, yet… I see these operations as tasks. And you may have a host of standard tasks. and then certain tasks occur at certain stations… (I do a lot of construction scheduling.) Really, each operation should be a separate line item in your database. What I would do is have a job table, or whatever you want to call it… Then as child records I would add each of the operations in an operations table…

Fine tuning this so it flows well would be pretty easy with all of the new features that Appsheet has been adding. Like having “Templates” for standard parts you make all the time, so that all the operations get added automatically… Assigning stations to various operations, etc…

[Smitster]

Thanks Grant,

So our current app setup is pretty similar - we’ve got a master Jobs table followed by a Operations table that effectively splits down to Operation 1, Operation 2 etc.

It’s the tasks within that operation that form the 50 odd columns on the form. So for example, the first step would be for an operator to clean the component, then set up the machine, then process the part, then inspect etc etc.

It’s these specific steps that are organised in the op/date/text column structure on the form

[Grant_Stead]

Then you need a third table of operational tasks…