tables and fields

[questionit]

Hi

 

I need help with creating tables.

 

I have following tables and fields:

 

Invoice: invoiceNo, company, total, paid, balance.

item : invoiceNo, itemName, quantity, price

 

Please advice if the categorization is correct or wrong - i am asking because i have getting a problem:

 

I have made invoiceNo as primary key in each table and i have linked these tables with this key.

 

I have created a form and added Edit boxes with these fields on it. The problem is that i am able to write on only fields which are in table 'Invoice' - but i cant write on fields that are in table 'item'.

 

Where have i gone wrong.

 

Please help

thanks

Qi

[AHunter3]

In the relationship between the two tables, check the checkbox on the Items side that says "enable record creation via this relationship".

[Josh Ormond]

I have made invoiceNo as primary key in each table and i have linked these tables with this key.

 

Maybe I am understanding this incorrectly. Is the invoiceNo the primary key in your items table? Is that possibly going to cause problems?

 

For sake of clarity (assuming the invoiceNo is primary key in only the Invoice Table), the structure should include the invoiceNo in the Items Table, and be related using that field, but the items should have their own primary key, itemNo or Id. Am I wrong on this and just reading it the wrong way?

 

This is also a good canidate for a join table.

[AHunter3]

I assumed the OP means "I connected the two tables by connecting these two fields".

 

There's no such thing as a primary key in FileMaker. It's a term carried over (inappropriately) from other environments that do have them. In FileMaker, a field can be an auto-enter serial number field. (But you can have several of them in the same table, although I don't know why you'd ever want to). In FileMaker, a field can be required to be unique (but again you can require that of many such fields in the same table). In FileMaker, a field can be a required value that every record must have (but it need not be unique, nor need it be a serial number, and once again the same table can have several such fields). And, in FileMaker, you can have a table with none of these types of fields (although many of us do think it's a good idea to have an auto-enter serial number field in each table, it's optional).

 

Having said all that, yeah, if the relationship is between two fields that are both auto-enter serial number fields, that's not how you're supposed to do it and it will not work for your (the OP's) purposes.

[Josh Ormond]

There's no such thing as a primary key in FileMaker. It's a term carried over (inappropriately) from other environments that do have them.

Sorry. Carry over from former apps.

 

I understand what you mean.

 

Having said all that, yeah, if the relationship is between two fields that are both auto-enter serial number fields, that's not how you're supposed to do it and it will not work for your (the OP's) purposes.

 

Yes, I was thinking that might be the reason the portal isn't working. I read it as, "My portal isn't working and not showing the right/any records". I missed this line when I read it late last night.

The problem is that i am able to write on only fields which are in table 'Invoice' - but i cant write on fields that are in table 'item'.

 

Fortunately, I understood the concept behind join tables before I started on my solution (thanks to your and the rest of the cafe). Saved me a lot of headache.

[questionit]

Hi all

 

I followed your instructions.

 

i get a strange problem now.

 

The fields which are not a 'Key' (or call it Primary Key), these fields display 'Unrelated Table' on the form.

 

The 'key' field is appearing to be Ok.

 

What shall i do with the 'Unrelated Table' thing?

 

My 2 tables seem to be OK and both of them are connected with field: Customer:: ID = Item::ID2

 

 

Thanks

Qi

[AHunter3]

I see how Customer is connected to Item via Customer::ID = Item::ID2, but how is Invoice connected to Item?

 

 

My guess, since you're seeing "unrelated table" errors, is "Not at all".

 

Shouldn't customer be connected to Invoice and Invoice connected to Item, instead?

 

Customers have invoices. Invoices have items. Items are of customers but only indirectly as a consequence of Items being Items of an Invoice which is of a customer, yes?

[Josh Ormond]

questionit,

 

Do you have 2 or 3 tables? In the original post you reference two. But in your last post you reference another table.

 

Try Ahunter3's suggestion, that should get you closer.

 

You may want to supplement your tables/fields with some of the following fields:

 

Customer Table: CustomerNo, Name, etc...

Invoice Table: invoiceNo, CustomerNo, total, paid, balance.

Item Table: itemNo, invoiceNo, itemName, quantity, price

 

Relate:

 

CustomerTable::CustomerNo ~to~ InvoiceTable::invoiceNo

InvoiceTable::invoiceNo ~to~ ItemTable::itemNo

[David Head]

Relate:

 

CustomerTable::CustomerNo ~to~ InvoiceTable::invoiceNo

InvoiceTable::invoiceNo ~to~ ItemTable::itemNo

I don't think that is what you meant at all. I think you meant:

 

CustomerTable::CustomerNo ~to~ InvoiceTable:: CustomerNo

InvoiceTable::invoiceNo ~to~ ItemTable:: invoiceNo

[Josh Ormond]

Yes. Wow, what was I typing? Someone must have switched the keys around on me.

[questionit]

Hi

 

Thanks for your reply.

 

Sorry, i actually have only 2 tables: Invoice and Item. Please ignore my mentioning of 'Customer' table, my mistake!

 

Now, i have connected :

Invoice:: invoiceNo to Item::invoiceNo

 

but still i see 'Unrelated table' message.

I think i am sue i have connected 2 tables with each other right.

 

This is driving me crazy!

 

Please help

thanks

Qi

[Josh Ormond]

Qi,

Are you able to upgrade your membership? You could post a sample or a screenshot of your relationship graph, so we can see what's up.

[AHunter3]

So... you were thinking of some other database when you referred to Customer and said it has a field named "ID" which is connected to a field named "ID2" in Items?

 

That's a very specific string of information to be a "mistake". May I ask you a question? Are you trying to "make things simple for us" by leaving out a lot of information because you're figuring it's not really relevant to your problem?

[questionit]

Hi

 

Sorry for the confusion.

 

Well, i was working on the same problem on 2 different databases. I mentioned both of them - i actually shouldn't have!

 

If someone give me his/her e-mail address, i can send the file for checkk-up.

Or if someone has an example of tables creation and relations to send me? ( ourjungle@hotmail.com)

 

I am unable to get FilemakerToday membership at this stage.

 

Do you think i am getting the 'Unrelated Table' message because i am using a Demo version of File Maker Pro 8.5 rather than purchased version?

 

Let me present you again with the present database detail:

Table1: Customer (Id, Name, Address)

Table2: Item (Id2, clothes, toys)

 

Customer:: Id = Item:: Id2

 

I have also checked 'Allow creation of records in this table via this relationship'.

 

 

Thanks

Qi

[Jack Rodgers]

Study up on how to create a portal and make your invoice items a portal on the invoice form layout.

[Josh Ormond]

The FileMaker help file has some good basic info regarding relationships and setting up portals.

 

You need to relate the ClientTable::ClientID = ItemTable::ClientID.

 

In your setup, each item needs to have the related client ID included to link the invoice to the client. The fields you have are fine. You just need to add the ClientID to the items table (and relate them).

[questionit]

Hi

 

Thanks

 

I have done it again in another new database and it works fine now. Wonder why was it not working in my previous database!

 

I have tried searching tutorial on 'Filemaker Portals' but looks like there isn't much relevant information on web about it. Can anyone give me a URL where i can learn Portal?

 

Thanks

Qi

[Josh Ormond]

In FileMaker help, search for "planning". Read the "About Planning a database" article. Then check out the "Creating portals to hold related records" article.

[questionit]

Thanks a lot