Total on portals

[Heathbo]

I have a layout with a portal with 15 rows. I want to get the total of all the values in the rows. How do I do that?

[-Queue-]

Create a calculation of Sum(yourrelationship::yourfield)

[Heathbo]

I tried this and the numbers I get back are all over the place. Sometimes it only sees the value from the first row of the portals. Other times it sees the first 10. The point is I can't figure out a way to make this acurate. Also how do you update it. It sometimes seems stuck at a number and refuses to update.

[Heathbo]

Plus when I have two or more of the same items on different rows the calculation only sees one.

[-Queue-]

See attached for a simple example.

SumPortal.zip

[Heathbo]

I have a join file in between the two records, could that have anything to do with the problem. The other problem I'm having is the claculation won't update. So the computer adds data from record 1 into record 2 when its not supposed to.

[-Queue-]

Here's a join file example. It seems to work fine.

SumPortal2.zip

[Heathbo]

Here's a join file example. It seems to work fine.

Its still not quite working. I've made some changes to your last attachment to reflect what I trying to do. After making the changes it just goes to hell. I would attach the file you provided with my modifications but this forum won't let me attach anything other than .gif,.jpg,.txt,.zip,.sit,.hqx,.pdf,.tar

[-Queue-]

So zip or stuff your file and then you can attach it.

Or tell me what changes you made, and I'll tell you what the problem is.

[Heathbo]

The attached image is the message I get when I try to upload. As for what I did.

I added another Portal onto the Parent list titled Serial and is on the same row as Amount. This new portal is a pop-up List that displays values from a new list I created. This new list uses the serial value from the child list. The join table I view in table mode. With these changes, in the Parent table, select the serial pop-up List and select a number. The amount should fill in (sometimes). In the row below it select the same number from the pop-up list. If you see the same issue I'm having you will notice that,

1. The amount dosen't always show up next to the serial number.

2. The SumChildAmount doesn't add up the two, it seems to count all references to the same record (though in seperate portals) as one.

3. Sometimes when I select a number in the serial pop-up list it will fill the same number in other rows below the one I clicked on.

Picture3.pdf

[-Queue-]

The attached image is the message I get when I try to upload.

That's why you need to use StuffIt or WinZip to compress files for attaching.

You added a portal inside the original portal, you added another field to the portal that is a pop-up list, or you added another distinct portal? Portals within portals don't work. You haven't said what relationship this new field or portal is using either. I think you'll need to attach it for me to understand the terminology you're using.

[Heathbo]

It worked, this time I was able to attach a file. It seems I can attach .zip but not .sit. Hopefully you can figure out what went wrong.

Thanks

SumPortal2.fp7.zip

[-Queue-]

You don't want the Child::serial field to be available. It is automatically set when a new Child record is created and it won't exist until you've entered data into a new portal row in Parent. As soon as you change the serial for an existing row, it is no longer related and you've killed your link between the two.

To answer your questions:

1. The amount isn't showing up on rows where you've selected a Child::serial that has no related join record. Parent is related to Child via Join. If there is no record in Join matching Parent::serial with the selected Child::serial, then there is no related Child record either.

2. If there is no related Join record, and therefore no related Child record, then there is no Child::Amount for that row to add.

3. Since you're allowing Child::serial to be selected, it is not unique. You're going to see as many rows appear as there are related records for that Child::serial, as long as there is one related record in Join which contains both that Parent::serial and Child::serial. This is why you want Child::serial to be unique, automatically created, and not modifiable by anyone.

It might help if you explained what you're trying to accomplish. It seems you may be trying to set up a Line Items table where you can create multiple records for an invoice using a portal and selecting the item id the way you're selecting Child::serial.

The way this is currently set up is very bad for your database.

[Heathbo]

Thanks for clearing that up.

This is what I'm trying to do: I will use pies as an example

I have a table (table 1) with 300 records. I have another table (table 2). In table 1 I have records of many different pies. The fields in that record are Name, Ingredients, Baking temperature, and ID #. Most of the records have different names, but there are a few with the same names but different ingredients and/or baking temperatures. Hence the reason I started using record ID #s. In table 2, I want to have repeating portals with a pop-up menu that will show me the names and ID#. On the same row as the ID # and name are portals for the ingredients and baking temp. When you choose a pie from the pop-up menu the data for ingredients and baking temperature fill in.

Using this example; I want to get a sum of all the baking temperatures. The problem I've run into is;

I may have three bluberry pies, all three are on different rows. Two are identical and the third has the same name and ingredients but different baking temp. Because the names are all the same the sum function only counts one record, even though there are three and the third one even has a different record ID#.

I hope this doesn't sound too complicated.

Any help you can give is much appreciated.

Heath

[-Queue-]

What do you mean by 'repeating portals' and 'On the same row as the ID # and name are portals for the ingredients and baking temp'? You can't have portals within portals, as I already stated. And 'repeating portal' has no meaning to me. Please clarify your terminology so we can understand what you are seeking.

[Heathbo]

Maybe this example will clarify what I mean.

pies.fp7.zip

[-Queue-]

I'm still not sure how you want the Ingredients and Baking Temp set up, because they're not in your sample.

[Heathbo]

Sorry, heres a new attachment. Instead of Baking temps and ingredients I just tried summing the filling ID. I get the same problem result I've been talking about.

Thanks again

pies.fp7.zip

[-Queue-]

You are summing based on the fillings table. fillings is related to pies via the join table. For each join record that has the same filling_id, there is only one related filling record. So naturally, it will only be summed once, regardless of how many times it appears in the join table. If you created a portal in pies based on the fillings relationship, you would see that there is only one row for each unique filling_id.

You need to base your sum on the join table instead, since that is what your portal is based on.

[Heathbo]

OK I fixed that. To the attachment I added a Calories field in the fillings layout. In the pies layout the calories are showing correctly, but their not summing up correctly. How do I sum up the calories?

Thanks again

pies.fp7.zip

[-Queue-]

Same issue. You need a calculation field in join of fillings::Calories that you can sum in pies as Sum(join::calcCalories).

[Heathbo]

OK, I got the fillings::Calories working. My next question is, how do I sum up just one pie in join. Also I don't follow you in Sum(join::calcCalories). I understand Sum(join:: but where does calcCalories come from? Is that coming from fillings or join?

Thanks again for being patient with me.

[-Queue-]

calcCalories is the calculation in join of fillings::Calories. This is the field you will Sum in pies to get the total calories for one pie.

[Heathbo]

YES!! It finally works! Thank you so much. Thanks for being patient with me.

[-Queue-]

My pleasure. Just don't ask me the same questions again.