calculation for 2nd -10th fields not appearing/disappearing

[Trevor Dalton]

Been a long long time (pre filemaker 7) since ive used filemaker and am trying to make a fairly simple database.  I am trying to calculate a number after verifying 2 fields.  Basically if A and B match relational database A and B, then I want it to take C divided by relational C multiplied by relational D.  The calculation I used is not working, I know I must be missing something stupid and obvious, but would appreciate some help.

 

the calculation I have is If ( A = Relational::A and B = Relational: ; C / Relational::C * Relational:)

 

I tried " " around the answer, and also the case function, but neither worked.   What am I missing?

 

Thank you

[opencirrus]

Hello Trevor, can't see the error in the calc - probably a little obvious but you do have the field type set to Calculation - yes? Otherwise, you'll need a trigger on an auto-enter.

 

If that doesn't work I would debug, using a test field in the target layout to make sure the relationships are working.

[comment]

The calculation I used is not working

 

"not working" is not a good description of a problem.

[Trevor Dalton]

what would you prefer? there are several to choose from A) the calculation result I am looking for is not what I was expecting.  or I am to stupid to figure this simple calculation out.  either one works, and I appreciate you taking the time to read the post so you could reply with a ridicule.

 

Just figured it out......The relationships that I had created I did using 10 of the same types of fields to the one in the relational database....so apparently I need to do one table for each relationship.....so I think the issue was it wasn't finding the match because all 10 of the fields didn't have the same data.

 

Thank you for your time to review the problem opencirrus

 

also on a note to any newbies that read this.....DONT DELETE THE TABLES. I had to start all over cause I deleted the main table and now all the records are locked with <missing table>  I couldn't figure out how to fix, but I was so fresh into it I just created a new one and started over

[comment]

I would prefer you post something something that would enable us to help you. First thing here would be the result you are getting vs. the expected result. Perhaps you are not getting a result at all. Perhaps you are getting an error. Or the actual number is wrong. All of these are important clues. There are others, but that at least would have been a start.

 

And why do you feel anyone is ridiculing you?

[LaRetta]

also on a note to any newbies that read this.....DONT DELETE THE TABLES. I had to start all over cause I deleted the main table and now all the records are locked with <missing table>  I couldn't figure out how to fix, but I was so fresh into it I just created a new one and started over

 

No, nobody is ridiculing you at all.  

 

But next time something strange happens such as this, post here first and ask.  The real answer is that you probably deleted the OCCURRENCE of your table in your graph.  If you did then the layout which was displaying that occurrence couldn't find it and displayed the <missing table>.  But your REAL table probably still existed (with its records intact).  You could know by looking at the tables tab and seeing if it was there.

 

If your TABLE was still there, you could create a new table occurrence of it in the graph, re-point your layout to THAT new occurrence (if its name was different) and repoint the fields if necessary or you could have just created a new layout based upon the new table occurrence.

 

In all, it is a struggle when learning something new and it in no way indicates intelligence or competence ... only that you are new at FileMaker.  We ALL have been there, trust me.  So hang loose and let us all help you.  If we ask questions it is simply because we need to know more information to assist you properly.

 

Welcome to FMForums, Trevor! 

[comment]

Just figured it out......The relationships that I had created I did using 10 of the same types of fields to the one in the relational database....so apparently I need to do one table for each relationship

 

I am not quite sure what you're saying here. Neither of these options sounds right.

[Trevor Dalton]

I had fields a1 thru a10 all selected off one table to the relationship A, versus 10 different tables (a1 - relational a) (a2-relational a) (a3-relational a) etc.....

[Trevor Dalton]

Okay....noob question #2, and I will try to give as much information as correctly as my limited skills allow......

 

In the main database I have fields named Ingredients1 thru Ingredients10 (10 different fields) along with fields in the main database named Portion Measurement1 thru Portion Measurement10.

 

In the relational database I have the field Ingredient and Serving Weight.

 

I setup the relationship in the main database with ingredient1 - Ingredient in the relational base. I also made 9 other relationship tables separately that also link the Ingredients to Ingredients  (Ingredient2 - Ingredient, Ingredient3 - Ingredient  etc....)

 

the Ingredient1 thru 10 fields can only contain names in the relational lookup file, and I want the Serving weight to be the same as whats in the lookup file as well, so I made the Portion Measurement1 thru 10 fields an if statement If (Ingredient1 = IngredientLookup::Ingredient ; IngredientLookup::Serving Weight).   For Ingredient2 I made:  If (Ingredient2 = IngredientLookup::Ingredient ; IngredientLookup::Serving Weight) and so forth for all 10 ingredients changing the Ingredient# to the corresponding #.  The issue is it works great on the first ingredient entered, and works if the 2nd ingredient is the same as the first, but as soon as you put a different Ingredient in the 2nd field, everything disappears (except the first)  If all 10 ingredients are the same it works fine, but as soon as the 2nd one is different it stops working after that point.  I have a feeling its a relational problem, so I created the other relationships and changed the calcs to reflect.  I also tried making an Ingredient2 and Serving Weight2 field in the lookup file, kept the first calc, and changed the 2nd calc to If (Ingredient2 = IngredientLookup::Ingredient2 ; IngredientLookup::ServingWeight2)  this didn't work either.

 

What am I doing wrong that makes Ingredient2 thru Ingredient10 not work the same?

 

[comment]

Could you start at an earlier point, please? What is your solution about? Specifically, what does a record in your "main database" represent in real life? And in the other table (at least I hope you mean a related table in the same file when you say "the relational database*")?

 

I think it's quite safe to say that if you have 10 alike fields numbered from 1 to 10, you are already on the wrong path. You should fix your tables and the relationships between them before you do anything else, as any efforts you expend going in this direction will be wasted.

 

 

---

(*) which is a technical term used for something else:

http://en.wikipedia.org/wiki/Relational_database

[Rick Whitelaw]

What Comment said . . . What happens if there are more than ten ingredients? Without knowing much about your solution, I believe I can safely say that rather than having ten ingredient fields, you should be creating related records instead. Much less limiting and more flexible in terms of reporting.

[LaRetta]

Hi Trevor, it would have been fine to stay on that same thread since, once you are structured adequately, your other question would most likely be answered.

 

In the main database I have fields named Ingredients1 thru Ingredients10 (10 different fields) along with fields in the main database named Portion Measurement1 thru Portion Measurement10.

 

What am I doing wrong that makes Ingredient2 thru Ingredient10 not work the same?

 

What is going wrong is that you are not taking advantage of relational theory (in the FileMaker way, which is a bit different than usual).  In FileMaker, when you see multiple numbers in field names, it means they probably should be records in a related child table.  I do not have time to provide a simple example but many others can do so.  Once you have each Ingredient as a related record, all of your other concerns about looking up #2 and the rest of the struggle will simply disappear greatly diminish.

 

You are at the most critical-understanding point in relational design ... when you have more than one of anything(children) for a certain something (parent), then it should be structured as:

 

recipe --<  ingredients_selected

 

You can search here for 'join' or I would be happy to give you a small demo file tonight when I get a chance.  All you need is a standard relationship.  Each table would have it's own auto-enter serial number.  Then in the Ingredients_selected table, you would add a Receipe_ID field.  Then join the tables on the Receipt_ID.

 

In the relationship graph between them, select its box and the at the bottom, check 'allow creation of related' on the Ingredients_selected side.  Put a portal based upon Ingredients_selected on your main layout.  Once you walk through these basics, you'll be up and running!

Rick and Michael (Comment) are both right that it would solve much of your problems.  I had responded prior but my post was sitting here unfinished.  So now you have three people willing to help you get the structure right.  :-)

[LaRetta]

You never indicated what the 'main database' represents so I guessed.  I suggest also you have a read here by one of the top Developers in this business (Comment):

 

http://fmforums.com/forum/topic/79927-creating-a-new-table-for-a-tab/  :-)

[Trevor Dalton]

Yes this is a recipe program, basically what I am trying to accomplish is I want to enter the ingredients and nutrional labels in the ingredient lookup file which has these fields: serving weight, serving size, ingredient, cost, calories, calories from fat, fat, carbohydrates, sodium, fiber, sugar, proteins.

Then in the separate recipe file, I have those same fields but #'d 1-10 to make them unique and am trying to tie them to the ingredient lookup field.  so when I enter in the ingredient it will only allow an ingredient that has already been entered in the lookup file (so I know that I need to enter the nutritional information) and then it should auto enter the servingweight (so that it is always using the same unit of measurement for the nutritional calculations. 

So in my recipe file, I have these formulas pulling information from the ingredient lookup file (keep in mind each one has 10 fields so I only listhing the 1st one, but the rest of them the field #'s are changed to reflect the appropriate field)

 

Ingredients1 thru ingredients10:  By member of Value List    Ingredient Lookup::Ingredient

 

Portion Measurement1 thru 10:  If (Ingredient1 = Ingredient lookup::Ingredient ; Ingredient lookup::Serving weight)

 

Calories:  If ( Ingredient1 = Ingredient lookup::Ingredient and Portion Measurement1 = Ingredient lookup::Serving weight ; Portion size1 / Ingredient lookup::Serving size * Ingredient lookup::Calories )

 

I wont list them all because they are just virtually identical for the remaining fat protein  etc categories, but the fields like ingredient1 is changed to ingredient2 and Portion Measurment2 and Serving size2 for the others.  (or whatever #1-10 is appropriate)

 

So the issue is, and it sounds like I don't understand the relationships correctly...when I enter in for example brown rice into ingredient1, all the calculations come up correctly in all the other fields.  When I enter in chicken into the ingredient2 field the calculations for the brown rice remains, but nothing comes up for the chicken.  If I enter in Brown rice in any of the remaing ingredient fields, the calculations come up for the brown rice again (even if chicken is still in #2).  So say I have brown rice in fields 1, 3, 6 and chicken in 2,4,5  the calcs come up for the brown rice but not the chicken.  If I switch the ingredient in Ingredient1 field to chicken, then the calcs would come up for the chicken in fields 1,2,4,5, but the brown rice in 3,6 would disappear.

 

I hope this is a better explanation, and I wanted to respond last night...but alas noobies only get a few posts a day (probably a good thing lol)

Thanks again for you guys taking the time to look at this really appreciated!

[comment]

A basic recipe database would have three tables: Recipes, Ingredients and Quantities.

 

The Recipes table would have fields that describe the recipe and only the recipe. Same thing about the Ingredients table. The Quantities table is a join table between the other two, and it would look something like this:

 

• QtyID (auto-enter serial number)

• RecipeID (foreign key to Recipes)

• IngredientID (foreign key to Ingredients)

• Quantity (number)

• Units

 

There would be one record for each join of Recipes and Ingredients.

 

This is a standard many-to-many data model, at least for starters. It can get a bit more complicated, for example if you want to calculate the required quantities from a serving size, or if - heavens forbid - you have ingredients that are recipes themselves.

[Rick Whitelaw]

This seems to me a situation where a real world example or explanation of the problem would work better that the A,B, Relational A etc. It's a little difficult to follow as you've described it. Once the folks here get a better understanding of what you're trying to do you'll likely get a lot of help from them.

 

Rick.

[Trevor Dalton]

OK...so I read the many to many relationships part of the filemaker pro the missing manual book (please don't think im not trying to solve these issues myself)  and I think I'm more confused.

Is what your saying is I need to create a file that just has ingredients, and then create another file that has the ingredients and all the other information im wanting to transfer (calories, fat, etc...)?

If that is what your saying Im not following how to link them, excuse the newbiness of the question, but why cant that be accomplished in the one (Ingredient Lookup) file that I have created?

[comment]

First of all, we're talking about multiple tables in a single file, not multiple files. If your book says something else, then it must very old (before version 7).

 

Is what your saying is I need to create a file that just has ingredients, and then create another file that has the ingredients and all the other information im wanting to transfer (calories, fat, etc...)?

 

That's not exactly what I said. I said Recipes, Ingredients and Quantities. They would be linked by two relationship defined as:

Recipes::RecipeID = Quantities::RecipeID

and:

Ingredients::IngredientID = Quantities::IngredientID 

Perhaps you should take a look at a basic join table demo posted here:

http://fmforums.com/forum/topic/50942-portal-grouping-problem/#entry239210

 

You could think of it as a parallel:

Contacts => Ingredients

Organizations => Recipes

Affiliations => Quantities

 

 

but why cant that be accomplished in the one (Ingredient Lookup) file that I have created?

 

I am afraid that would require a very long explanation. Let me just point out a few things:

 

If you have many alike fields, such as Ingredient1, Ingredient2, etc. then you need to search in 10 fields in order to find a recipe that uses a given ingredient .

 

To perform a calculation using an ingredient, you will need to define 10 calculation fields, using practically the same formula. To modify the calculation, you have to modify those 10 fields. That's a recipe for generating errors (if you pardon the pun), not to mention the amount of work.

 

Most importantly, this structure ensures that data is entered and stored in one place only. No matter how may recipes use rice as an ingredient, there will always be only one record describing rice.

[Trevor Dalton]

On the "If you have many alike fields, such as Ingredient1, Ingredient2, etc. then you need to search in 10 fields in order to find a recipe that uses a given ingredient"

 

I made an IngredientSummary Field and made it a calculation: List ( Ingredient1 ; Ingredient2 ; Ingredient 3 ; Ingredient 4 ; Ingredient 5 ; Ingredient 6 ; Ingredient 7 ; Ingredient 8 ; Ingredient 9 ; Ingredient 10 )

 

then I can just enter in one or more ingredients in there and it will find all the records with those ingredients

 

on your second part "To perform a calculation using...." I did that, made 10 different calculations for each field

 

on the third, I made the ingredient lookup file's ingredient unique, and the ingredient in the recipe file has to come from the ingredient lookup file.

 

I think I attatched what im working with here so you can see what im saying, no real data in it, just working on structure at the moment

 

Ingredient lookup.zip

[bruceR]

This is a mod to your file that shows the approach Comment and others have tried to describe.

 

It is incomplete but it's a start.

RecipesBFR.fmp12.zip

[Trevor Dalton]

wow I think im so over my head right now, last time I used filemaker (pre7, talking like the first macs with the floppy drives in the screen) I thought you had to do separate lookup files for each thing.  This looks like you did it all within the same file.  what is the box that the ingredients are in?  Im really trying to understand this, will digest what you did for a bit and maybe it will make more sense to me.  Thank you very much for a working example

[bruceR]

That is a portal. The standard way of viewing one-to-many records.

The file now has three tables, along the line prescribed by Comment.

Recipes, RecipeQuantities, and Ingredients.

[Lee Smith]

The subject of Recipes or ingredients come up from time to time. Here is a recent post that may help you. “My Recipe database needs help"

 

HTH

 

Lee

[LaRetta]

I had fields a1 thru a10 all selected off one table to the relationship A, versus 10 different tables (a1 - relational a) (a2-relational a) (a3-relational a) etc.....

 

Reg flags are going off on your design, Trevor.  Whenever you have fields number, such as a1 ... a10, it usually suggest that those fields should be records.  I'm unsure if you ever resolved this but I highly suggest you have your base structure reviewed before you get too deep into it, which would mean zipping and posting your file.  I see you going down the wrong path that many folks newer to FileMaker go down and that would be a real shame since it will increase your work by 100 fold AND you may even hit a dead-end and have to start over because of it.

[Lee Smith]

Hi Trevor, and welcome to the FM Forums,

 

I agree with LaRetta, 

 

I have a feeling that you are using old school techniques in version 13. A file will help see us see your schema and better assist your in moving your solution forward.

 

Lee

[bruceR]

Wasn't all this dealt with a week ago in his other thread?

 

He provided an example file that had fields A1 A2 etc; I modified it to use a relational design; he expressed thanks for the help.

 

http://fmforums.com/forum/topic/93431-calculation-for-2nd-10th-fields-not-appearingdisappearing/#entry427349

[LaRetta]

Ah.  I never looked at your post, Bruce, so I didn't know.  Obviously neither did Lee, LOL.  I just had this sitting in my pile of 'things to review'.  At least we hope now that Trevor is on the right relational path!  

[Lee Smith]

I didn’t make the connection between a receipt and a calculation after verifying 2 fields.

 

@ Trevor, Please do not multiple post questions.

If you need additional help about the same file, please post that in a reply to the original thread.

 

I am going to merge these two topics unless I hear a strong and sensible reason not to.

[LaRetta]

And I've been too pressed to be here very often this week.  I think moving it is fine ... one situation lead to the problem in another problem and we believe Trevor is working through it, now re-directed a bit.  But in a sense, it is good they are together because they both deal with structure and what throws new FM folks the most.  He is picking it up very quickly so I doubt it'll be a problem for him either.  Thank you, Lee!