[Solved] Form fields where "data field" dynamically chosen

[Alejandro]

Hi, I'm new with Base. Hope I explain this well...

I created a table to collect data from Scientific publications with the following fields:

* Author
* Date
* Latitude
* Longitude
* Method

Then the table has about 200 extra fields. They are species names with a default value of zero. For each record, I need to complete all the fields listed above, but only a few species fields will need to be edited. As you can imagine, it's not practical to have so many fields in a form. So I was wondering whether is possible to set up a bunch of fields in the form, let say 10, where I can dynamically select the data field, and then edit the value. For example, in given record, only Canis familiaris and Felis silvestris were found, in abundances of 5 and 7. I would like then to select these two species in the "open fields" and set the values to 5 and 7. The rest of 198 species will be zero by default. The species selection process could be done with a dropdown list, predictive text typing, or other smarter method!

Well, so far I managed to create a form with Author, Date, Latitude, Longitude and Method fields. But no idea on how to set up these "dynamic open fields".

Is that even possible? If so, how I can do it?

Thanks in advance.

[Villeroy]

You suffer from poor database design.
https://docs.microsoft.com/en-us/office ... escription
https://en.wikipedia.org/wiki/Database_normalization

[Alejandro]

I see your point, sorry for the lack of experience. What's your proposal for this kind of database then? At the end I will need to export a 2D matrix with all these zero values to perform my analyses. So I will need to have the 200 species fields in each entry somehow...

[UnklDonald418]

I attached a simple demonstration of how you could construct your database
There are 3 tables
"pubs" is similar to the table you have already created
"species" is a new table containing all the species, I populated my table with just a few values, but could easily hold the 200 you mentioned.
"pubsXspecies" is an intersection table, a specially constructed table the allows you to have a many to many relationship between the other 2 tables. In other words each record in "pubs" can be associated with from 0 to as many "species" as are in the "species" table AND each record in the "species" table can be associated with 0 to as many "pubs" as are in the "pubs" table. Each entry in this table in unique, no duplicates will be allowed.
There is one Form document that demonstrates how to enter data into those tables.
From there you can write queries to do your data analysis.
I included a few simple queries to demonstrate some possibilities.

Demo57_Species.odb

(17.04 KiB) Downloaded 453 times

[Alejandro]

Hi. That's exactly what I was looking for, and "intersection table". Even if I didn't know what I was looking for! I didn't know what was the queries for... Just realized they will do the trick to export the matrices I need for the analysis afterwards. In summary, many thanks! I will create my own database, based on your example

[MrProgrammer]

At the end I will need to export a 2D matrix with all these zero values to perform my analyses.

Here is how you produce the 2-dimensional table from your database. I reference the following attachment, where I show three tables from a database, the result of a query from the database, a pivot table, and the final result. Though some of the data is actually in a database, I show it all in a spreadsheet so I can make one attachment. These are the sheets:
• Table.Pubs: A copy of the pubs table from the earlier database to show you the data I used
• Table.Series: A copy of the species table from the earlier database to show you the data I used
* Table.PubsAndSpecies: A copy of the pubsXspecies table from the earlier database to show you the data I used, modified to add an Abundance field
• Query.Lookup: A database query which uses a JOIN of the three prior database tables to gather the needed information in one collection
• PivotTable: A pivot table created in Calc with the Query.Lookup sheet cells as its source
• Zeros: A simple sheet created from the pivot table using the N() function to turn empty cells into zeros for export

202203191950.ods

(19.71 KiB) Downloaded 463 times

I would build the query in the database, access it in Calc with View → Data Sources, then drag the query result into the Query.Lookup sheet.
Calc Guide Chapter 10 - Linking Calc Data

The pivot table takes me less than a minute to create. If you have not used one before, you will want to read the guide. You can see how my pivot table was configured by clicking any cell in it and using Data → Pivot Table → Create.
Calc Guide Chapter 8 - Using a Pivot Table   (formerly called Data Pilot)

Your spreadsheet will have only the query data, the pivot table, and the Zeros sheet since the other data is in your database. After you have saved the 3-sheet spreadsheet as an ODS file, you can export the Zeros sheet with its 2-dimensional table to CSV with File → Save As → (scroll down for) Text CSV, then use the CSV for your further "analysis". This small example uses only seven species. If you have a thousand, your pivot table will be much wider to show them all.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.