portal

[eledal]

Hi,

I'm a beginner...I need to create a database for the veterinary clinic where I work. I need to have a list of all matherials and therapeutics used in day hospital (such as syringes, different antibiotics and so on) with prices for unit used and I need to have the possibility to make an invoice of the different items used for a given dog. I tried to create a portal to view different items coming from the list of matherials with respective prices but something's wrong and it doesn't work...could please someone help me explaining step by step smiley-cry what I should do? I have filemaker pro 8.

Thank you very much for your kind help

Elena

[Maarten Witberg]

this is a case of many-to-many. one pet can have many treatments and one item can be administered to many pets. one pet can come back many times. So you need four tables:

 

[b] pet               invoice             invoicelineitems         treatmentitems[/b]
[i] key fields[/i]:
petID ----------< petID
                InvoiceID ------<     InvoiceID
                                       ItemID      >-------------- ItemID

[i]content fields [/i]    
                date of visit           treatment               item name
pet name          anamnese                quantity                  item price
owner name                               item total price
owner address   invoice total amount               

 

 

this is just the database structure. building the interface is the next step. but it is important that you famiiarize yourself with how relational databases work. If you need further help, please post again.

[eledal]

thank you very much! I did create different tables, a client table with a client id, a patient table, with both patient id and client id, an item table with patient id item id and prices, and an invoice table with invoice id , item id and client id

What I can't do is to have more than one item in an invoice...I can't make a portal work.

thank you very much for your patience smiley-smile

[Maarten Witberg]

What I can't do is to have more than one item in an invoice...I can't make a portal work.

but I can't see what you're doing right and wrong... so either you need to upgrade your membership so you can post a sample or you must describe in detail what you're doing.

[Josh Ormond]

If it helps to look at a sample of a join table in operation, do as kjoe suggested and upgrade your membership. It's worth it.

 

If you do that, here is a sample of how one works (from comment).

[eledal]

ok...I upgraded my membership and I looked at the example but I still don't know what's wrong.smiley-cry

...I try to explain what I need...I need a table for clients, a table for patients (which can be more than one for a given client), than a list of "items " (matherials used or services) with prices, and an invoice table where I can recall client name and more than one item with respective prices to calculate the invoice.

I created four tables: clients, patients, items and invoices.

Fields in table clients: client ID and client name.

Field in table patients: patient id, patient name, client id (matching field), client name (related).

Fields in table items: item id, item, item price.

Fields in table invoice: invoice number, id client, client name, item id, item price.

Then I made the following relationships

id client in client table with id client in both patient table and invoice table

item id in item table with item id in invoice table,the I inserted price in invoice table as a lookup from items table. I clicked on the relationship between item id and I selected allow creation of records inthis table (invoice table) via this relationship

 

the portal has to have id item, item description and price...I just get a blank portal, i'm not able to recall or to write anything in it

Thank you again for your supportOpps!

[Maarten Witberg]

OK can you post a screenshot of your table occurrence graph?

thanks for joining :-)

[eledal]

okay, I attach a picture of what it looks like..it's in italian but I think that it can be understood anyway

I think I checked on the wrong side when I had to "allow creation of record in this table". The portal is no longer white and i can select an item on the first row but when I try to select another item from the second one, the first changes as wellOh Really!

[eledal]

and I can't select more than three items even if there are 5 rowssmiley-surprised

[Maarten Witberg]

I think you need a join table between fatture and items. this would be the lineitems for fatture (invoice). Lineitems are different things than your items table holds: these are the basics such as item name and item price. But the line items will hold quantity and line item total (qty * amount for item). The way you set it up now, you will relate the items to many different invoices, so if then you change it for one invoice, you change it for all. So make the join table Fatture Line Items and your issue should be resolved. In invoice, show Fatture LineItems in the portal, not the items

 

[u][b]
Fatture          Fatture LineItems         Items[/b][/u]
ID fatture ------< ID fatture
                  ID Item >-------------- ID Item

[Josh Ormond]

eledal,

Here is sample of what I think you are trying to do. This structure should work. There are a few more things I would add to it, but this should give you the idea.

 

Check out how the tables and relationships are connected. Remember, you don't need to create a field "Client Name" in the tables other than the Client table. You can still place that field on the Patient layout. See the sample.

 

Also, note the relationship between the items and invoice. Any questions, let us know.

[eledal]

thank you very much....now it workssmiley-laughing