Run same calc for many fields at once?

[FiMaRookie]

I have over a hundred fields of data. I put together a custom function/calculation that will convert all values with 5 or more characters into scientific notation. Right now, I'm just going one by one, converting all the fields into calculations, and plugging in this function and replacing the "number" parameter with the name of the field.

Is there a quicker way to accomplish this? Obviously if I compiled all the data into one field i woudl only have to run the function once.... I have it separated into diff fields according to categories, but I suppose if I just keep a record/key of the order of the records and the corresponding serial ID# for each category, I could still reach my desired end goal (of displaying the one piece of corresponding data from each category depending on which subject I pull up).

[Vaughan]

When you say "convert" do you mean "change their value" or "make them display differently"?

If you change their value, then 3,657,491 might become 3.6574 E6 -- which is a different number, and this could cause problems in calculations somewhere else (I could see an accountant getting very upset about that missing $91).

On the other hand, you can just make the number *look* different without much effort: set the field's number format to "General" and it will display larger (and smaller) numbers in scientific notation.

[FiMaRookie]

When you say "convert" do you mean "change their value" or "make them display differently"?

If you change their value, then 3,657,491 might become 3.6574 E6 -- which is a different number, and this could cause problems in calculations somewhere else (I could see an accountant getting very upset about that missing $91).

On the other hand, you can just make the number *look* different without much effort: set the field's number format to "General" and it will display larger (and smaller) numbers in scientific notation.

How do I change it to general?

Hey Vaughan,

thanks for the reply. u'll notice I basically deleted my original question because I more or less figured it out, simply incorporated an "if" calculation, "length" (# of characters in text) is > 5, then run the Scientific Notation function.

However, you bring up a good point. I suppose it would be ideal to preserve the original value, but change the display (although for my purposes it is not absolutely imperative that I do so).

[comment]

I am not sure I understand exactly what you mean. Perhaps changing:

Case (

number = 0 ; 0 ;

to:

Case (

Length ( number ) < 5 ;number ;

might do it.

[FiMaRookie]

I am not sure I understand exactly what you mean. Perhaps changing:

Case (

number = 0 ; 0 ;

to:

Case (

Length ( number ) < 5 ;number ;

might do it.

Thanks for the reply! Yeah, it was pretty simple that works perfectly. Now I'm wondering if there's a quicker way of running this function rather than going one by one for every field ( i have over a hundred, lol).

[FiMaRookie]

I want to run a simple function/calc "If ( Length (number) > 4 ; ScientificNotation ( number ; 2 ) ; number )" where SciNotation changes the value to Scientific Notation to 2 decimal places.

I have hundreds of fields of data. Each field represent a different category of data. There are also about 100 subjects (therefore 100 records for each field), so I want to more or less display the one piece of data from each field (via portals or value lists) that corresponds to whatever subject I happen to pull up.

Anyway, I'm very much a beginner. So right now my impression is that I have to go one by one to each field, change it to a calculation, then plug in the function (and replace the "number" parameter with the field name).

Is there a quicker way?

I know I could probably compile all the data into one master field, then play around with the serial ID#'s to make sure the display fields show correct data (i.e. show the 1st, 101st, 201st, 301st, etc. values from the master field when I want to see the data corresponding to subject #1). But ideally I just find a way to run this calc/function for all the fields quickly, since I do like having all the categories of data separated.

[FiMaRookie]

When you say "convert" do you mean "change their value" or "make them display differently"?

If you change their value, then 3,657,491 might become 3.6574 E6 -- which is a different number, and this could cause problems in calculations somewhere else (I could see an accountant getting very upset about that missing $91).

On the other hand, you can just make the number *look* different without much effort: set the field's number format to "General" and it will display larger (and smaller) numbers in scientific notation.

ok nevermind I found how to format to general. however, i think i read somewhere that filemaker's default setting converts to scientific notation if the number is bigger than 10^9 or smaller than 10^-9 and ideally I'd like this to be like 10^5 to 10^-5 sicne I have so many fields that I want to fit in one page

[comment]

Perhaps you should have 100 related records in another table instead of 100 fields.

[FiMaRookie]

Perhaps you should have 100 related records in another table instead of 100 fields.

It's 100 x 100. 100 subjects, 100 categories, 100 pieces of data in each category (each corresponding to 1 subject)

I know I could probably compile all the data into one master field, then play around with the serial ID#'s to make sure the display fields show correct data (i.e. show the 1st, 101st, 201st, 301st, etc. values from the master field when I want to see the data corresponding to subject #1). But ideally I just find a way to run this calc/function for all the fields quickly, since I do like having all the categories of data separated.

[comment]

It's 100 x 100. 100 subjects, 100 categories, 100 pieces of data in each category (each corresponding to 1 subject)

So you will have 100 records in the Subjects table, 100 records in the Categories table, and 10,000 records in the Data join table.

[FiMaRookie]

So you will have 100 records in the Subjects table, 100 records in the Categories table, and 10,000 records in the Data join table.

...yes, 10,000 total records. However, I entered the categories as fields NOT records. And I split up the categories into several tables of about 20 fields each.

What's a "data join table"?

I don't even have a subjects table per say, since all the data for each category was entered in the appropriate order corresponding to subjects #1-100, so I simply added an "indexed, auto-serial ID# field" to every table, which essentially is the subject column. I also use the "indexed, auto-serial ID#" to join the related tables. The relationships have been set, and they work when I tested various portals/value lists of data for various subject #'s. The only problem now is getting that scientific notation calc./function to work for every field of data. I'll probably just end up going one-by-one tomorrow....oh well

[Vaughan]

have to go one by one to each field, change it to a calculation

Um, no. Changing the field to a calculation will destroy all data in that field.

Like in Excel, a field can either have a value entered, or a calculation that returns a result based on other fields... but not both at the same time.

[comment]

However, I entered the categories as fields NOT records. And I split up the categories into several tables of about 20 fields each.

Well, I am not sure what your solution is about, but that doesn't sound like a good approach. The problem of adding a hundred identical calculation fields is only a symptom; you can expect more serious issues if you don't fix your data structure.

What's a "data join table"?

A "join table" is a table that resolves a many-to-many relationship into two one-to-many relationships. Using your example, it would have (at least) these fields:

SubjectID

CategoryID

Value

[bcooney]

I've merged the duplicate threads, hope that doesn't cause more confusion. In the future, please do not double-post. As Comment points out, your structure is wrong, and this will haunt you the entire project. If you can, take the time now to redesign your solution so that you have a join table (a table that resolves your many-to-many relationship between Subjects and Categories). While you're at it, make sure that you have primary keys in the Subject and Category tables with text description fields, and store their foreign keys in the Data join table.

[FiMaRookie]

Ok, thank you all for your replies!

Yeah, right now I have primary keys for every table of Category fields. (so about 5 or 6). And I Just have relationships between all 5 or 6 and the table where I want them displayed.

I will go back and restructure as you guys have suggested...