Easy way to add multiple formula fields?

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered
I need to add 200+ formula fields to my app.  I really don't want to add the fields manually then go into each formula field property and paste the formulas in. I know if you import a dummy record with the field headers, the import gives you the option of "Create New Field", however, a formula field is not a field type option.  Is anyone aware of an easier way to import formula fields?  Any help would be greatly appreciated.
Photo of Brian

Brian

  • 0 Points

Posted 4 years ago

  • 0
  • 1
Perhaps there is a design change in your app which would eliminate the need to have so many formula fields?
Photo of Brian

Brian

  • 0 Points
This was my first approach, and unfortunately this is not an option.
How about Good Music or a really bad TV show and copy the formula 20 times and then Save and Next your way though the list to edit the formula.  Then repeat 10 times.
Photo of Brian

Brian

  • 0 Points
I'll take that as a no.

Thanks for the quick response, Coach.
Photo of dwhawe

dwhawe, Champion

  • 908 Points 500 badge 2x thumb
I find that caffeine helps in these situations.  ;)
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,224 Points 20k badge 2x thumb
Yes you can do it with a short script and the QuickBase API. Basically you call the following two API methods in loop 200 times:

API_AddField
API_SetFieldProperties

However, the names, field type and formulas have to be supplied somehow and unless the names (eg [field001], [field002, ... [field200]) and formulas (eg [field] +1, [field] +2, ... [field] + 200) are related you are going to have to supply the random field names and formulas via some type of text file. A minor inconvenience but still quite doable with script and the API.

But I have to agree with Mark that a design change might be in order as I can think of a good reason why you would need 200 different formula fields in one table unless you were doing some type of scientific or engineering application.

Be that as it may be, I remind the gentle readers of Attwood's Law and its QuickBase corollary:

Atwood's Law
Any application that can be written in JavaScript, will eventually be written in JavaScript
http://cryptochaos.com/atwoods-law-any-application-that-can-be-writ

QuickBase Corollary to Atwood's Law
Any QuickBase application that cannot be implemented in native QuickBase can be implemented using JavaScript



Photo of Brian

Brian

  • 0 Points
Dan,

Just wanted you to know that I was able to solve my problem using your guidance above, and itDuzzit.  I just made a google sheet with all my fids and formulas and hooked that up with QB using API_SetFieldProperties.

I read through all the different QB API calls and there doesn't seem to be one to change field types, which is a bummer, but if I ever need to change existing field types, I could just use the API to remove the fields and then re-add them as a different type.  But then again, that would wreck any existing forms I was using with the original fields.

Thanks again, Dan.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,224 Points 20k badge 2x thumb
>Just wanted you to know that I was able to solve my problem using your guidance above ...
Well you will definitely be interested in one of the presentations at the Mas Que Nada QuickBase User Group conference entitled "Base de dados de rápida estilo livre Formulário Juggling usando a API FormData" which I think means (my Portuguese is a little rusty) "QuickBase Freestyle Form Juggling using the FormData API." It is pretty clever if you ask me. The author shows you how you can take any QuickBase form and manipulate it at will adding, removing and modifying field values using simple methods such as append(), delete() and set() and then submitting the altered form under script control. One interesting thing is that the technique isn't even limited to the forms that manipulate table data - you can actually modify QuickBase administrative forms with ease. You can do some amazing things in just a few lines of code such as (1) take an existing form for one table and apply it to another table, or (2) construct forms on the fly with a repetitive structure. There is a video in the presentation is nothing short of spectacular ...

Brazilian Freestyle Juggling Competition
https://www.youtube.com/watch?v=o4bI-GuyH7I

Using The FormData API
https://developer.mozilla.org/en-US/docs/Web/Guide/Using_FormData_Objects
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,224 Points 20k badge 2x thumb
>I read through all the different QB API calls and there doesn't seem to be one to change field types, which is a bummer

Why limit yourself to only official sanctioned corporate API methods? Liberate yourself and use the Be the API technique:

Pastie Databsase
https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=385

Notes:
(1) Use the appropriate character code to specify the type of the new field
(2) know what you are doing and test outside your production application first as the conversion is not undoable
(3) no apptoken needed(4) Inspiration for Be the API Techniquehttp://totallyabsurd.com/bethebike.htm