Hi Rounak Jain,
From what you are describing I come up with 4 different tables.
a. Invoice.......................one to many relationship to LineItems
.... 1. Invoice No
.... 2. Date (Invoice)
.... 3. Customer Name (Number)
.... 4. Item Name (Number)
.... 5. Item Qty
.... 6. Item Rate (By using a lookup)
.... 7. Extended Price (Qty * Rate)
c. Product.........................one to many relationship to LineItems
.... 8. Item Number
.... 9. Item Name
.... 10. Item Rate
d. Customer.....................one to many relationship to Invoice
.... 11. Customer Number
.... 12. Customer Name
.... 13. Customer Address
How many fields you really need is dificult to tell. Think of every relevant information.
For instance if your customer has a billing address which divers from the delivery address, then that is relevant to track.
If the current item has not been sold before to the current Customer, then it must not show anything.
I can not figure out what you mean with this. To me it seems you are not selling new item to customers.
An other thing which you can do is purchase yourself "The Missing Manual" and/or the "Filemaker Training Series" if you haven't done this already. Please don't be offended. Not my intensions. But these are really nice peaces of work. The part about ERD in the FTS you should definitely pay attention to.
I have spent my Sunday learning. I have reached close (atleast I think).
I have started with the Invoices Starter Solution. I have added a Table Occurence for Products "Products 2". I have created a join table "CustomerProduct". It has four fields CustomerID, Product Name, CustomerProduct ID and Price. In the Customer Table, I have added a Portal for CustomerProduct with the fields Product Name and Price. I entered some products and prices in the portal. Then I checked CustomerProduct table and I could see the desired result. Now, I have a customer-wise distinct price for every distinct product in the CustomerProduct Table.
In the Invoices Details Table, there is a "Unit Price" field. I want it to retrieve the Price from the CustomerProductTable. What Formula should I enter in the Calculate Field or how should I use the LookUp field? Kindly help me. Here are some images:
I am now able to retrieve the prices as I wanted in the post above. I added a field "Customer ID" in the table "Invoice Data". I specified its calculated value to $$CURRENT_CUSTOMER_ID.
I added a Table Occurrence of Invoice Data "Invoice Data 2". I set up two relationships;
InvoiceData2::Item-CustomerProduct::ProductID (ProductID actually contains item name. Sorry!)
Then, I set up lookup for unit price as:
Starting with Table: Invoice Data 2
Lookup from Related Table: CustomerProduct
Copy value from field: Price