Invoices

[Anuviel]

Me again, sorry need a little help.

Here is the stripped down version of my file with the necessary parts for my question only (or so I believe).

Question:

Items table is related to LineItems via Auto-enter serial number and LineItems is related to Purchases via Auto-enter serial number as well.

Due to needs of people it is set up so that multiple companies can go onto one PO. Basically a user enters ItemCode into portal and the next field is a list of available Vendors for that item. Once the Vendor is entered Primary key is looked up (via auxiliary relationship) and the rest of the information is automatically filled in. All of that works perfectly (thanks to all that helped with it yesterday).

When all of the information is entered the user can hit print and the LineItem records will be sorted by PO and Vendor (separate after each occurance) thus printing items that belong to one Vendor on one Page only - effectively separating them.

I want to be able to automatically pull the data based on Vendor into a new Table or a list layout.

Basically on Purchase layout you can see:

PO 001

A001 Vendor1 $4

A002 Vendor1 $5

A001 Vendor2 $3

When that prints it comes out as:

PO 001

A001 Vendor1 $4

A002 Vendor1 $5

and on a new page

PO 001

A001 Vendor2 $3

I want to have a layout (list layout) that will show only

Vendor1 Total

Vendor2 Total

Once the user clicks onto Vendor 2 they would go to Purchase layout again but this time it would display only records related to one Vendor, the one the user chose (this I know how to set up) - what I do not know how to set up is a list layout (browse mode) that will automatically separate original PO by PO and Vendor like it does for a report thus showing this on the list

PO 001 Vendor1 $9

PO 001 Vendor2 $3

hope it makes sense.

Thank you.

Example.zip

Edited March 25, 2008 by Guest

[SurferNate]

If I am reading this correctly, you want a layout based upon your Purchase_Line_Items join table. That's the only location where you will have individual records for each vendor=po relationship. Just specify the parameters for your find.

Now, if you want summaries, that's another simple trick. Relate Purchase_Line_Items to itself (lets call this new TO "Purchase_Line_Items_similar"), where Vendor_Name=Vendor_Name AND PO_Number=PO_Number

Then just create a calculated field in Purchase_Line_Items - evaluated from Purchase_Line_Items - SUM(Purchase_Line_Items_similar::TotalCost)

Edited March 26, 2008 by Guest

[Anuviel]

Thanks, I tried that however the records came up twice. On the new layout as suggested I got "duplicate records" not really duplicate but:

In LineItems I have

PO 001 Vendor X Item A001 (1 record)

PO 001 Vendor X Item A002 (1 record)

when I made a self-join by PO = PO and Vendor = Vendor

on that new layout I got two records

PO 001 Vendor X Item A001

PO 001 Vendor X Item A002

and

PO 001 Vendor X Item A001

PO 001 Vendor X Item A002

I am trying to get only one

PO 001 Vendor X Item A001

PO 001 Vendor X Item A002

Still working on it...

[Anuviel]

Ok I have added some things to the file and I put in the keys manually so that i can show what I want to achieve for end result.

Check Purchase layout. There is 1 PO and 4 items ordered (crating 4 line items records)

Check Invoices layout. There are 3 records. This is what I want to achieve automatically. I entered the keys manually. How do I get the keys entered and records created automatically on Invoice table?

When looking at the Purchase layout note that Item A and Item X are ordered from same Vendor thus creating only 1 record in Invoice table.

Example.zip

[SurferNate]

EDIT:

Well, what you show me looks like all you need is a simple lookup. You have a one=many relationship from Invoices to Purchase_Line_Items. One Invoice record is related to more than one dissimilar Purchase_Line_Item record.

Are you trying to summarize multiple similar records into one line on Invoice? (ie. you have several Purchase_Line_Item records with the same Invoice, Item, and Vendor keys?)

Try this logic maybe, you can use the result of one relationship to determine the result of another relationship. If you want to return only unique records, do that first, and then set a key field only if the test passes. Note that unstored keys can usually only be in the parent table (the one from which you evaluate your calc), not in the child...

You can also use this method to determine specific records which you want to contain a calculated value.

EDIT AGAIN:

You know what, I'm still a little confused. I think the piece of the puzzle missing is when and how you want to generate an invoice. Do you first create the invoice record and then add Purchase_Line_Items to it?

Edited March 26, 2008 by Guest

[Anuviel]

Thanks. This is what I am looking for.

On purchase layout I want the user to enter all of the items that they want to order. Same item can be ordered more then once and from more then one vendor. So basically the user would enter item code and vendor and the rest would fill automatically (that part works). It would look:

Item1 Vednor1

Item2 Vendor1

Item2 Vendor2

Item3 Vendor5

Then I need an invoice layout. When a user goes to that layout he sees:

Vendor1

Item1

Item2

in one record.

In second record he sees

Vendor2

Item2

and in 3rd he sees

Vendor5

Item3

I know how to set it up so that it prints as described above but cannot get it to display like that while in browse mode.

Before you edited your last reply - that seemed like the stuff that I was looking for.

Thanks.,

[SurferNate]

So you are generating an invoice immediately after purchase entry? Therefore you have only one related set of records that are not connected to an invoice yet?

You are more than welcome to study my messy solution Summarize Grandchildren. It is under tips & tricks. I have to say that the logic strained me so it's probably not terribly easy to sort out.

I am sure that in there the answer you need lives, however it uses at least two or three techniques that are very structurally dependent.

I think the answer is you need a variable relationship from invoice to Purchase_Line_Items to filter only the records you want (you can do this I'm sure), and then a script/button to set your invoice key to the appropriate related Purchase_Line_Items. If you need to determine how many invoices to generate, first get the count of related vendors, from that relationship, and create that many new invoice records, and then loop through them to set the relationships.

[Anuviel]

and then a script/button to set your invoice key to the appropriate related Purchase_Line_Items.

Thanks. Yes, I tried using an un-stored calculation for a relationship but could not due to the fact that it was on the child side. I guess I will have to go via a script so that once the purchase is entered the user hits "create invoices" button and thus sets the calculated id on the child side. I am pretty sure that it will work that way however before I try that I will study your summarize grand children method and ugos's portal summary method to see if I can get it to work that way.

thanks for the help.

[SurferNate]

Here's an idea, maybe you can carry it to fruition without much explanation.

Make a special a relationship/calc that predetermines which invoice record each new Purchase_Line_Item should relate to. This can even generate a unique key for each of any number of invoice records that do not exist yet. I do this using SerialIncrement(nextChild::childID; 1).

Then generate new invoice records with a script, and as part of that loop, set each invoice with your newly created invoiceID keys.

Edited March 26, 2008 by Guest

[Anuviel]

Thanks, what I was thinking via script is this:

Currently when a LineItem record is created I have an AutoEnter calculation (let's call it InvoiceID) field which takes the PO number from purchases and adds VendorID to it.

Like:

Purchases::PO_Number & Vendor_ID

So all records in LineItems table Ordered from one Vendor will have a unique ID tied to the PO from which they were ordered.

Once the Purchases are entered and the user clicks on View invoices button I would have the script go into the LineItems table, preform find for all records with the PO number as a search criteria then it would copy the InvoiceID from the first found record and it would set that InvoiceID into a variable. It would then go to Invoice layout, create new record and set the key filed to InvoiceID thus completing the relationship. It would then go back to the found set and search for a different InvoiceID then one in the variable and repeat the steps with exit after last. Hope this makes sense. It would also have to check if the InvoiceID already exists in the Invoice table...

Hope this makes sense. I will try to make it, will post later as to how it went.

Thanks.,

[Anuviel]

Made the script. It works like a charm. Exactly what I needed.

thanks.,