Grouping Data In Portal: Why is this so #$%! hard!

[Digital Deviant]

OK, coming over from Access and Fox.

Here's my problem,

I just need to display grouped data in a portal. I have 4 tables:Technicians, Invnetory, Transactions and Locations. When I move an Inventory Item from one location to another the transaction is recorded in (oddly enough) the Transactions table. When displaying a Technician I want to display what the technician has in his Inventory based on Location. This all works. What doesn't work is when I display the list in a portal I see all of the transactions listed even if they are the same part number. All I want to do is display the items grouped by ItemCode, then sum the Quantity. I have tried many times to follow the instruction in the FM help file. I have search and search but there is only one topic I found that, while it seems may address my issue, is spoken FM'ease and I don't understand it.

I am frustrated beyond compare. While I know the softeware is different from what I am used to, I can't believe the things that I find should be simple are so freakin hard.

Relationship is: TechCode (Techs) to itTo (Transactions) One to Many, Allow Create

ItemNumber(Inventory) to ItemNumber(Transaction) One to Many, Allow Create

Locations are handled through a LookUp

Thanks for any help,

DD

[RalphL]

Welcome to the Fourm,

Can you post a sample file, maybe a clone with some data added? It looks like you are doing a query with a relationship. This will require another TO on the relationship graph for this relationship.

[Digital Deviant]

I will do that soon. This thing is a mess right now with alot of "test" things set up so give me a day or two to make it more respectable. This last hurdle comes at the end of my development and I was about to start some clean up. It is around 500K, will that be OK?

DD

[bruceR]

Well, not only is is hard, it's impossible! >>If<< I understand what you are trying to do - then you have chosen the wrong tool for this particular job. Portals are lists of records meeting certain match criteria, and portals to do not have any data-grouping features. Reports with subsummary parts are probably what you want.

[sebaroo]

I feel for you. At least, if I understand what you're getting at. I stumbled across this discussion searching for a way to trick my portal into showing grouped infos (a summarize data button in the edit relationship window would be nifty), without having to created a complicated 3-table solution with scripts.

I have an invoice table and a line items table. The line items sometimes have different tax rates, and I would like to see in the totals of each tax rate in a portal on my invoice layout. Of course, printing is not a problem - here they've provided a summary function.

If you find a solution, let me know!!!

[Digital Deviant]

Sorry for the delay everyone. Was called out of town - business emergency.

Here's what I am trying to do specifically;

I have multiple technicians that each have their own warehouse location (car stock). When a customer service request is completed I need to distinguish where to pull the parts they used from. I am able to display the items the technician has through a simple join between customerID and techID. I pull the records for technician warehouse from a transaction table that is compiled as items are moved to and from the Main warehouse. The problem comes when displaying the linked records for the customer to tech. It shows all of the separate transactions with the quantity for each separate transaction as well. Giving this some thought it would seem that a separate table for each technician would be the smartest move. But then how do I move records and deduct/increase the quantities in each table without creating duplicate records (which can't exist anyways as part numbers are unique)? In Fox I would: Define a Recordset, Open Recordset, Find Record, Update Fields, CloseRecordset. Anything similar in FM? Any suggestions greatly appreciated. I would hate to have a fixed number of warehouse locations though.

Thanks.

[Ender]

A portal shows one line per related record, so to create your portal of things grouped by ItemCode, you will need a table of ItemCode records. You will then need a script to loop through the Transaction Line Items and create an ItemCode record for each unique ItemCode for that transaction.

Or if this is for print purposes only, then follow BruceR's advice and use a columnar report with sub-summary parts based on the Transaction Line Items table.