Is there a way to use the collection field so we can add as many lines as we want, and each new line works to tie in the database individually?
Do you mean when you add a new line, the fields in the new line is different as the first line? What the use case of this?
Haven't tried it, but you could create a field for each column of the database, load all the values from the database into those fields. Then use javascript to load the values from each field into a set of arrays and loop through the arrays, populating the collection fields, adding as many collection sets as you need. Maybe there's an easier way to accomplish this?
Use case would be this:
Using a collection, the amount of Requests wouldn't be fixed like it is in the table. Each drop down is referenced to a column in a database. The Price is also referenced based on the two choices. Category example could be staples. and Supply would be the size of staples. This determines the Price. The quantity also updates the total. Javascript is used to do that calculation.
If I could modify this instead as a collection, I would be able to not have a restricted amount of Requests. That's my end goal.
You should still be able to use a table for that. Just check the "Range of rows" radio button and set the range of rows from 1 min, leaving max blank. Then users add as many rows as they need.
Ok. For example the Total field, I would just need to sum all total fields together and submit into a Grand Total field.
Would this be done through forms calculator? Or javascript for it.
I would actually use a table for every category, that way I can add more characterstics to every item.
Because of that I can also add locations, then it would be simpler to even have a bird's eye view...
Maybe put graphs, so it's visual about the status of items...
Just my two cents.
Something like what they do, but only the inventory part
The supply is the variance for the category. This is already a system that is in production. I just wanted to see ways to make it more dynamic than it is already at the fixed 10 fields.
The issue also lies with how the data is transferred to the client. Passing each piece would most likely need to be a multivariable field? if there are a variable number of fields that could be made in the Table.
Both table and collection can have as many lines as you want if you leave the Max as empty for range of rows/range of sets as empty. But for performance issue, you may restrict the number of rows/sets not exceed 500. You can use lookup rules to auto populate the category/supply/Qty field, such as:
1. fill category with data source column1;
2. match category with data source column1, fill supply with data source column 2;
3. match category with data source column1, match supply with data source column 2, fill Qty with data source column 3;
And use Form calculation to calculate the total(Forms 10.1 support this) within same row:
=Mult(index(Requests.Prc,row()),index(Requests.Qty,row()))
Use Form calculation to sum all rows of Total into Grant Total:
==SUM(Requests.Total)
Thanks I'll give that a test!