Invoice via Repeating field or Portal - Thank you for helping.

[Anuviel]

Hi, I would appreciate if someone could help me out with the matter below. Thank you in advance.

I am making a simple DB for the office. It consists out of two tables. Please see below, field names are in Bold.

Table 1

Company

ItemCode

ItemName

ItemCost

Table 2

Company

OrderDate

OrderBy

OrderTotal

The tables are releated through Company:

Table1:Company = Table2:Company

I want to do the following:

In Table2 I want to insert a portal or a repeating field (whichever would work better). The Portal would have the field ItemCode from Table1 - that field would be formatted as a member of value list (Value list would contain Field ItemCode from Table1) and would be a pop-up field.

So basically when I click into the first field in the portal a pop-up list would appear containing all of the ItemCodes from Table1. Once I select the ItemCode the rest of the portal row would automatically fill in related information. Then if I selected the second Portal row and chose a different ItemCode or the same one for that matter the information would be filled in automatically.

I have gotten so far as to have a portal and a pop-up field and all other fields that I want to have are in the portal as well. The problem is that once I create a new record Portal gets automatically filled in once I type the company name in Table2 (if that company name exists in Table1).

The problem might be somewhere in the relation between the tables - not sure, or it might me something totally different.

* In the above example I used Portal but repeating field would be good, I accomplished the above in the repeating field however for the 1st repeating field only, if I type the code in the second it all gets messed up.

** I know how to accomplish above by creating many-to-many relation with as many fields as I want to repeat it however I think there has got to be a better way of doing that.

*** all of the above - just trying to create a simple invoice system.

Thank you very much, hope I made sense, if not let me know so I can improve.

[AudioFreak]

Stick with portals for sure. Do not go the repeating field route.

You should change the relationship to the items table to contain a Purchase Order Number and relate the items based on that. You should also NOT use company name for any relationship and use a Company ID Number. I would also create another table for Customers and pull in the info from that table via lookups based on the Company ID Number.

Other than that I think for the most part you are on your way.

Basically I would do what you are doing using 4 tables.

Customers(Related To sales Orders based on Company ID)

Sales Orders(Related to Line Items on Po Number)

Line Items(Related to Inventory on Id Number)

Inventory(Items you sell)

HTH

Edited June 1, 2007 by Guest

[Anuviel]

Thanks, this is pointing me in the right direction - what do you mean by Line Items? I am new, i just started using FM about 2 weeks ago and, even if I am saying so myself, am pretty much (basic stuff and so up to speed).

Simple example:

Now let me see if this break down is correct:

ItemTable (Inventory)

ItemID

ItemName

ItemCost

InvoiceTable (Invoices)

CompanyID

CustomerID

InvoicePO

(portal that will display line items?)

LineItems

PoID

ItemID

Do not know what to put here, not sure IE. Line items would be same as the Inventory items which I could pull into the portal?

Relations:

InvoiceTable::InvoicePO = LineITems::PoID

ItemTable::ItemID = LineItems::ItemID

Am I going in the right direction,

Thanks a lot for helping

[AudioFreak]

Line Items would be each individual Item that was on the order. By looking them up rather then directly relating them. You will be able to change the price of the items later and it won't effect your prior orders.

Fields needed would be:

Po_Number(Auto entered through the portal

Item_Number(You choose in the portal in Invoices)

Item_Description(lookup based on Inventory Relationship)

Cost(lookup based on Inventory Relationship)

The relationship to the inventory would look up all but the Po_Number. The Po_Number will be autoentered when you add an item in the portal in your invoice table.

[Anuviel]

Thanks, will test it out/try. Will post later if I was able to do it or if I got stuck again.

[AudioFreak]

Here is a quick File I threw together to help you out a bit. I Might have missed something, but it should get you started.

Michael

Invoice_Example.zip

[Anuviel]

It works, awesome. Gave me a much better understanding of relationships as well.

Thank you so Much, this solved my problem. Really appreciate it.

Now I can finally finish up the project, thanks.

[AudioFreak]

Glad I could help }:(

[Anuviel]

One more quick question - If I wanted to restrict the Part_Numer only to display the Inventory Items that come from a certain Vendor what would I have to do to make it with current tables or should I make one more set (another portal and Vendor table) - that is for Ordering purposes, first one was for selling purposes.

thanks

[AudioFreak]

The Purchasing File we have here is basically one long list of all the items we have ordered from all vendors. Just search for the vendor you want to see. If you wanted to see the different vendors you purchased the same part from, you would just search for the part number.

Our Purchasing file has 1 portal on a seperate Layout and that is used for printing the actual Purchase Order(Printing Portals can be tricky).

lol Sounds like your file is going to Snowball into a big project.

Edited June 1, 2007 by Guest

[Anuviel]

Actually yes, it is a part of a bigger solution that I am making, the Inventory layout, list view and navigation is already done. The problem that I had was with the portal (not anymore thanks to You).

The thing is that (in the file you sent me) once you click into the first field on the portal you get a drop down box of all inventory items. That is fine for Sales.

When placing an order to our vendor I want when the same field is clicked on, only the items that are from the vendor that we are placing the order with show in the drop down. i realize I will need a new table called Vendors. However, after I am done with that and set the thing up, how would I restrict so that only the items (inventory items have a filed named vendor) show up in the drop down box excluding the items that are not bought from that vendor.

Having a huge amount of different items - scrolling down the list is quite tedious.

[Anuviel]

Went the other way. Switched the field from pop-up list to edit box so the item code can be typed in. This will hopefully solve the problem having the people go through the list every time.