Multiple Linked Records and Formulas

[oreocereus]

I am trying to design a record keeping system for a market garden. 

One of the key pieces is recording events that happen to a garden bed - e.g. transplanting, seeding, harvesting, bed preparation events. The record is the date of the event.

I am having trouble making a simple system for when there are multiple "parts" to an event. E.g. the "preparation" event has 4 inputs (which come from an "input" record table, which I would like to be able to break down & summarise the costings of. 

The user would select the name of the input and it would look up the APPLICATION RATE(KG/m3) the BED SIZE receiving the amendment, the COST/KG. This is then summarized in a formula which multiplies APPLICATION RATE (KG/m3)*COST/KG*BED SIZE. 

This is simplest if I can make the input field link to multiple records - however, my formula for costings won't work if there are multiple records in each of those look up tables.

Is there any way to deal with this? I am new to this kind of stuff, so I'm sure there is. Making 10x "input 1, application rate of input 1, cost of input 1 kg/m3, cost of input 1" fields seems messy and complicated for the user.

[TheTimeSavingCo]

Hmm, I'm assuming you have a table where each record is a single part?  If so, I would move the formulas into that table and then use a rollup field in your "Events" table to summarize stuff

Could you provide some screenshots of the relevant tables and fields please?

[oreocereus]

Hi, not sure you mean a table where each record is a single part, but here is the bed history table. I also have the same issue for seeding, and will have the same issue for transplanting - those are the 3 events that happen to beds (preparation, seeding, transplanting). We practice multicropping, so there are usually different species in each bed, and I want to be able to record notes on each of them (e.g. how many grams of seed, spacings of each crop, etc). 


Bed History

Here is the INPUTS table where the name, costings, application rate, supplier etc is recorded

Not hugely relevant, but there is BEDS table which is there as a lookup in other tables because 1) bed names will change as the garden grows (so I can just adjust the name on this table and still have my data from other tables be relevant) and 2) because there are some beds of different sizes, and I want to quickly calculate different square meterage in other tables. 

[oreocereus]

In addition to simplifying the records (and thus making them more useful), I would like to simplify the input - so if there is a "bed preparation" form, I would like the user to be able to select from a drop down "compost, potassium sulphate and lime" (for example) rather than that needing to be 3 separate drop boxes.

I would also like them to be able to select multiple beds when doing this form, and have it assign the event as such (so these would be seperate records from one input form) - so if the user preps 10 beds the same way, they don't do the form 10x.

[TheTimeSavingCo]

Ah, I see.  I'm afraid to do this you're going to need a junction table between Beds and Inputs, and users would select the bed they're inputting stuff into and what they're inputting

In said junction table you'd have lookup fields to grab the bed details and the input details, allowing you to use your formula to calculate cost since each record is a single link between the bed and input. 

To allow users to select multiple inputs, you could create a new table (let's call it 'Forms') with a form where the user selected a single bed and the inputs they wanted.  You could then use an automation that would trigger on form submit, and use a repeating group on the list of Inputs they had selected.  In that repeating group you would create one record each in the junction table mentioned above with the appropriate links to the Beds and Inputs tables

To allow for multiple bed selection you could create another automation, and it would use a repeating group on the list of Beds the user selected, and for each of those it would create a single record in the "Forms" table where each record was linked to one "Bed" and the multiple inputs.  You'd need to modify the previous automation so that it would trigger on the records created by this automation, thus doing what you're looking for

Heh I know it's a lot, but it should work and it's fairly simple to set up!

[oreocereus]

I am slooooowly getting there. I am having trouble with the bed automation - when I set it up and submit the form for say "bed 1 and bed 2" it creates 3 records - one for "bed 1 bed 2" one for "bed 1" and one for "bed 2" - so I need to stop it creating the "bed 1 bed 2" record..

EDIT: Ok, so I added a field in "bed prep form" table that counts the "beds." Now the automations go:
When a form is submitted, create record in "Bed Prep Form" with input date, inputs and beds. It creates individual bed records against each bed (and a surplus record which contains all the beds the form was submitted with). 
Then I have another automation that goes
"if (count of beds =1)" then "create record in "bed prep" with an individual record for each input.

Does that seem like it'll cause any problems? Having the surplus record in "bed prep form" feels a little messy, so I wonder if there is a cleaner way

[TheTimeSavingCo]

Hmm...I think I might set it up as:

Automation triggers on new record creation
Conditional that checks how many "Beds" are linked to it
  - If >1 bed, create one record per linked Bed in this table with all of the linked Inputs
  - If 1 bed, create one record per linked Input in the junction table

And that should work fine? 

Apologies, I don't really follow your setup