File attachment version

[Greg Spink]

I have a data base to help me organise my formulation work.

Ideally want I wanted to do was have formulation built in to a record. By using the one to many field to pull in some of the details of each ingredient. But I then wanted to specify quantities of that ingredient in the formulation to get the cost of the final product by the kilo. Because I can’t see a way to do this as the one to many form seem to only allow you to pull in information from the record. But because each ingredient might be used in many formulations I need to specify the quality in the formulation record. If there is a way to do this in tap forms please advise.

As I can’t do this I have been using the file attachment and doing this work in numbers. Not idea as I have hand write the ingredients and values and have to change every time a price changes across all formulations.

I now have the issue with version. As if I take too long editing the numbers document it will not correctly save to the data base. This means I have to do lots thing to get the document re-saved to the record.

Is there a way of resaving an edited file in the acctahced file field consistently?

Or is there a way to have a spreadsheet tap form where I can build the formulation using ingredient records and then in the new record specify the quantities used in the formulation.

I hope this makes sense.

[Brendan]

Hi Greg,

For this purpose I would recommend using a Table field instead of a Link to Form field.

You would still have your Ingredients form, but with a Table field, you can select an ingredient and Tap Forms will copy the values into the Table that has matching field names. That is, you would setup your Table field to have the same field names (or as many of them as you want) from the Ingredients form. Plus you can add additional fields, such as a quantity field.

Now you can add a calculation field to use the quantity to compute some other values.

As for the File Attachment field, the monitoring of changes is handled by the File Attachment field itself when you edit an attachment. If that File Attachment goes away by visiting another record, then changes will no longer be monitored and won’t be saved back to the database.

Thanks,

Brendan

[Greg Spink]

Thank you Brendan.

I am now using the table field for the formulation. But all the date is being manually entered. The totals and percentage of the ingredients is automated in a calculation.

DO you have a guide that explains how you add ingredients from another record and enter details into a table where you can then add new details to that table to create the calculations needed?

[Brendan]

Hi Greg,

On the Table field properties there’s a function to connect to another form. There’s a setting there called Copy records from.

Select your other form that contains the ingredients in the popup. Now when you see your Table field in your record, click on the checkmark button and the list of records in the Ingredients form will display. Click on a record and the values from the fields that match by name will be copied into a new Table field record.

[Greg Spink]

thank you so much that is excellent.

[Greg Spink]

I have noticed that when using link to form when you make a change in the original form the link forms also up date.

But when you set up a table and copy from a record it does not update when the original record is updated. Is there a way to get a copied record in a table to update when the original record is updated?

[Brendan]

Hi Greg,

That’s the main difference between Link to Form fields and Table fields. They are built that way by design. If you want to update the record in the Table field, you’ll have to select it again from the popover list of records (if you’ve connected your Table field to another form).

The record values in a Table field are copied and not referenced like they are in Link to Form fields.

Thanks,

Brendan

[Greg Spink]

Thank you for your support Brendan.

Ok. This does not achieve quite what I want to achieve with these forms and records.

This is what I want to achieve

I have a set of form called Ingredients. Each ingredient record has a number of different data stored.

I then have a form called formulations. In formulations I want to select a number of different ingredients. An ingredient may be used in a number of different formulations records. In each formulation I will require a weight in % to that specific formulation. I want to calculate the cost of the formulation which I currently have setup using tables. As such I do need the tables to update when the cost per kilo changes to get up to date costs on the formulation. I can’t do this on a linked form because I can’t add additional details and if I did change weight to a formulation it would be the same on all formulations which of course would not be the case. IS there another way of getting the functionality of a table where I can create new fields ie weight and also get the cost updated when the main record is updated?

Can scrips help or can the system be setup differently that enables this?

[Brendan]

Hi Greg,

I think the Table solution should work for you. The benefit of the Table solution is that once you’ve selected the ingredients to copy to the Table field and entered the quantities, the prices for the ingredients is solidified into the Table field. This is historical data. The price on Monday when you made the recipe could be different than the price of the ingredients on Friday when you made it again. So you wouldn’t want to have the prices for the ingredients change for every recipe you used those ingredients before.

So I guess I’m not sure why you want to update all the prices in the already entered records of the Table field.

You can pretty much do anything you want with scripts. So you could write a script that if you changed the price of an ingredient, you could loop through all the records and Table field records and update the price for the already used ingredients. It would be a bit of a complex script though.

[Author]

Posts