epace

Report based on single Table help

Discussion created by epace on Nov 8, 2018
Latest reply on Nov 8, 2018 by Jaymo

Hello, I am trying to create a report that shows specific data from a single table. This table is changed due to a daily script run on the server to change dates, add data, and such. It is a very simple table with a Unique Key that is auto generated, a foreign key to our inventory table, 2 date fields, and then some counts and average fields for the report.

 

I need some help to figure out the relationship I need to create for this table to get what I need to accomplish to work.

 

Here is what I need to do. I want to have a drop-down box that shows all of the Foreign Key field values that are in the table. Simple enough and working. With this drop-down box though, I have 2 fields that I want to display. One is a Count of times that this Foreign Key shows up in the table, and one with an average of the count field that is linked to each particular occurrence.

 

To make sense with this last one, I am checking our inventory daily for those that are going below our minimum quantity that we have set that we want to have. If it is below that amount, it adds a new record in this table with the Inventory ID number, and the date that it went below. This same script that is running to find these, also looks at all of the ones that are already below to see if we received more of the item, and if so put in a date for the receipt back. There is a count that is then auto calculated that shows how many days out it was.

 

So, my report will take a look at these four fields to give data to help us figure out what and where we are having bottlenecks with our inventory purchases.

 

I currently have the table linked to itself in a relationship based on the Foreign Key, since that is what will be driving all the other data on the report. However, when it is showing the data about this Inventory ID, it is sometimes showing the same item 2 times. I put in a portal to show the data I have, and it changes the data in the portal based on that drop-down menu for the Inventory ID, but on some of the ID's, not all of them, it shows the same thing 2 times, which is throwing off my total count numbers for some of them. I am sure my problem has something to do with either the relationship or the Portal Filter calculation.

 

The portal filter calculation is just PrimaryTable::InventoryID = SecondTableOccurrence::InventoryID

The relationship is the same thing.

 

Do I need to add something somewhere? If I try to add the PrimaryKey=PrimaryKey to the relationship, it only shows 1 occurrence in my portal instead of all of them. Any help you can give me will be great.

Outcomes