 | | Including items in edit form | | Sometimes having a parent table and a details table with related records might not be the best presentation from the user’s viewpoint. Consider the following situation:
I have a menu engineering application to calculate the cost of each dish. So I start with an INGREDIENTS table (containing information like package, weight and price) and a DISHES table. Each dish typically has up to 5 ingredients.
So I could set up a details ITEMS table that will hold, for each dish, a list of its ingredients, with the amount to be used, and will calculate the cost of that ingredient. Then add the cost of all the ingredients to know the cost of each dish.
That works very well from the perspective of the database structure, but it might not be very user-friendly.
For each dish, the user needs to:
- CREATE the dish. Fill in the basic info, then SAVE.
- CREATE the 1st ingredient item. SAVE.
- CREATE the 2nd ingredient item. SAVE.
- CREATE the 3rd ingredient item. SAVE.
- Same with 4th & maybe 5th ingredient.
- EDIT the dish record again, set the selling price according to the total cost of the ingredients, then SAVE.
Altogether, you have anywhere from 3 to 7 EDIT and SAVE operations, or anywhere from 6 to 14 button clicks for each dish.
So for this kind of situations, where you have a small and limited number of details, I’d like to propose a different kind of set up, without an ITEMS table.
Since creating and saving each ingredient item in a child table is inconvenient for the user, I created in the DISHES table 5 reference columns to the INGREDIENTS table, one for each ingredient.
So now you CREATE the dish, select the ingredients while in the edit form, write the amount needed of each ingredient, see the total cost of the dish right there, write the selling price of the dish and SAVE. Only 2 button clicks, one to CREATE, one to SAVE. It makes a world of difference.
With this kind of setup, I ran into a snag when I wanted to see which dishes used a particular ingredient. In the INGREDIENTS table, in the preview screen, I wanted to have a single details view that would show all the dishes that used a particular ingredient. Not 5 details views, but a single one. It’s of no importance whether a dish uses an ingredient as ingredient 1, ingredient 2, etc.
This is how this can be set up:
- In the INGREDIENTS table, add a Formula-Text column named RecordId with the formula: RecordId()
- In the DISHES table, for each of the 5 ingredients, create a lookup column that reads the RecordId value. Let’s call these lookup columns RecordId1, RecordId2, etc.
- Create a view in the DISHES table (let’s call it Ingredients Usage) with the following filter formula: [RecordId1]=URLParam(URL(), "id") or [RecordId2]=URLParam(URL(), "id") or [RecordId3]=URLParam(URL(), "id") or [RecordId4]=URLParam(URL(), "id") or [RecordId5]=URLParam(URL(), "id")
- Go to the INGREDIENTS table and create a many-to-many relationship between the INGREDIENTS and the DISHES table. For the second table (DISHES) details view, select the Ingredients Usage view that you just created.
That should do it.
| | | Comments |  | Pierre 9/22/2021 12:37:14 AM Hello Jorge
Thanks a lot for sharing your experience ! It is great to see some activity in this forum.
I have run into the same issue as you before (not with ingredients, but with components of an order).
On the edition mode:
I had used a similar approach as yours - with 5 pointers in the mother field.
- Userwise it is easy to use
- It was a bit painful to set up as you have to manage the view/hide of each pointer (you do not want the user to select an ingredient in field 1 and 3, but not 2)
- And you are limited to a given number of ingredient per dish.
Later, I preferred to use an approach with a subtable (ITEMS), with the 'inline edit' option activated [TeamDesk might upgrade this inline edit option to make it smoother].
On the search feature, I adopted another approach
- In the mother record, create a 'Concatenate' text summary field with the 'ingredients' details
- create a text field, with a default value formula = to the concatenate records (the text field is searchable, not the concatenate one)
- update the concatenate field each time an ingredient is added / modified / suppressed.
Kind regards,
Pierre
|
| | Back to Search Results |
|
