Repeating Fields and Individual Entries 1

[Shanksta]

Hi,

I recently created a database for use in a laboratory setting where chemists have the option of making a mixture with up to 5 chemicals, each of which has its own price, supplier, etc... I wanted to expand on this and make a pricing table with 5 repeating fields also. There is also a sum at the end to find the total coast for production.

The problem: When I enter in the prices the calculation goes through and everything works. When I move to edit the next record the chemicals which information I already entered does not show up. Only the first field in the repeating field set stores the price and supplier of the chemical.

Is there any way to fix this so that if I enter info for the 3rd field in the repeating set it will still retain all of the information?

Thanks.

 

[marty1452]

I've read your post many times since it was posted. I don't quite understand your solution and I thought someone else may have the answer for you.

Since no one has responded to your question, I thought I'd give you some information. Though this may be off target for you what you need.

You mentioned that you enter in the values and then they disappear when you move to the next record. This happened to me with the first database I ever built. I didn't have a clear understanding of the layout and the data structure. I designed this cool form but when I entered information and selected new record, a light turned on for me. In a single database structure, a new record should always give you blank fields.

Information you wish to use again from record to record needs to be in it's own database. Then you connect the two databases with a relationship. The project a chemist is working on would be one database, the chemicals used would be another. If you are tracking who is the chemist and which project he was or is involved in, you may have a chemist database too. So the data structure may look like this, Chemist.fp5, related projects, project.fp5, chemicals used, chemicals.fp5.

I may be way off target here and if so, it's because I don't have clarity of your problem.

I hope this helps.

Marty

 

[Shanksta]

Thanks for the response Marty -

That same light also turned on for me....

I was always used to designing everything in Access and kept neglecting the fact that Access basically stores all those different tables as part of the same database. I tried to achieve the same thing in Filemaker but forgot to make totally seperate files.

Thanks again

 

[Shanksta]

I was also wondering Marty, if there is either a filemaker action or if I could write a script that would renew the data in the database from the relationship.

So if I change the price of the product in the products database it would automatically be the same in the database its related to....
right now i see that I have to go back and just select it again.

Thanks again

 

[marty1452]

I'm not that familiar with your solution yet but here are some things you should consider.

If you have a field in the Project.fp5 database for a chemical selected and the price of that chemical, would you want that price to update with a price change or would you want it to stay the same for any existing projects and change to the new price on any new projects?

A calculation field is going to update when the price is changed in the price database.

A lookup field will lookup the price and stay that way after the price is changed. An exception to this is if you re-select the chemical in an old record, the price will be re-looked up and today's price will be brought in.

These are both done through a relationship, not a script. It sounds like you are new to FM so I'll explain. The relational ability of FM is one of it's stong points in my opinion.

One way to do this is create a chemical field and a price for that chemical in the chemical database and the project database. The chemical database is going to be a text and number entry field. In the project database they will be a calculation field with a text or number result. (if the above answer is that you want the price to always update). The calculation is done like this: Select the price field in the project file. For the calculation, on the top left of your window, use the drop down button and select "define relationship". Select New. Now select the chemical.fp5 file, assuming you've created the chemical.fp5 file. Now select the chemical field from each file. Now select the price field, from the relationship. Your done!

A lookup is done in a similar fasion but under options in the field definition.

I hope this helps.

Marty

 

[Shanksta]

Thanks Marty but the relationships work fine, though you did mention what I meant:

"A lookup field will lookup the price and stay that way after the price is changed.  An exception to this is if you re-select the chemical in an old record, the price will be re-looked up and today's price will be brought in."

I want TODAY'S PRICE to appear without re-selecting the chemical in the old record.

Do you know what I mean?

 

[marty1452]

I thought I mentioned that above. A calculation will do that. A lookup won't.

If the old price still shows up in a calculation field, a script may be needed to "update" the field. An update script could be Enter Preview Mode, Enter Browse Mode. Doing this causes a screen refresh and updates the calculations. In some cases I have written scripts that "update" like this:


Project script:
Perform script(external, chemical.fp5,main layout)
Enter Preview Mode
Enter Browse Mode

Script Main layout in the chemical file:
Screen Refresh

The first script calls the second script. It does matter much what it does. Having a script go from the project file to the chemical file will also update the values.

The script will auotmatically return to your primary file once the Perform script has run. The user will see a screen refresh. They won't see that the screen changed to another file and came back again.

Marty

 

[Shanksta]

Thanks Marty

Workrs very well and I didn't know thats all you had to do!