importing a csv file into a portal

[mantral]

Hi Guys,

 

I have been studying the Business Pack Solution, invoicing part from Filemaker.com.

 

I am trying to import an excel file or a csv file into the portal of the invoice. Every time I try the line items table and other tables are grayed out. It only defaults to the main table. I would like to import to the line items table which is a portal in the invoicing layout. Where in one client has many orders.

 

thanks

 

Mantral

[LingoJango]

You need to go to a layout based on the line items table occurrence.

[mantral]

Thanks...

 

I just created another layout with the fields from the table that compose the portal.

 

I was able to import the fields from the excel file into the line items table. But the problem is the imported file does not show up in the portal of the invoice layout.

 

Any help is greatly appreciated

 

Mantral

[LingoJango]

When you run the import, the line items need to have some kind of reference to the invoice ID in order to be linked to a specific invoice. I don't know what the field name is - perhaps something like kf_invoiceID - but that information need to be placed in the line items you import, somehow.

[mantral]

Thanks...

 

I am linking them to a unique field called customer_id. Since this is an invoice where a customer will order multiple products.(line items table)

 

I did the link already and the import went well. It imported into a new table that I created named line items. Where I have the date purchased, description of item and price. And another table which I called customer_info.

Thats two tables total.

 

But in the main form based on the customer_ id table where I have the customer information and the portal of the line item tables. It did not automatically fill the customer_id field and the portal is blank.

 

But when I imputed the customer_id number in the main form the portal then reflects the contents of the line item table.

 

Is there a way when I import into the line items table the customer_id number field in the main form will be filled up automatically getting the value from the line items table.

 

thanks

[LingoJango]

So you won't allow a customer to have more than one invoice? Line items should definitely be linked to invoices first and foremost, not directly to customers. Customer ID for an invoice, if present (probably unnecessary), should be a calc based on the invoice customer ID.

 

Can you provide a list of your tables and the fields in them, and a sample of the data in your csv file?

[mantral]

Hi,

 

Of course I want multiple line invoice for a customer.

 

I have two tables.

1st table for customer info with fields like lname, fname, address, city, state,zip.

2nd table for line items with fields like date purchased, product description, qty., price.

 

thanks

[LingoJango]

You're talking about multiple invoice lines. I'm talking about multiple invoices.

 

One customer comes into your stationery store and buys two staplers and a box of candy. That's two line items, but one invoice.

 

The following month, the customer comes back and buys a ream of paper and a plastic in-tray. That's a separate invoice.

 

So you need a third table called invoices, unless you are planning to issue a separate invoice for every single item purchased by a customer.

[mantral]

Hi Lingojango

 

Thanks for the reply.

 

Yes I am talking about multiple invoice lines and multiple invoices.

 

The multiple invoice lines are in the table "line item". Originally if you buys staples and clips from me I will issue him invoice #1 for his first purchase. But he does not pay this transaction yet.

 

The customer customer comes back and buys notebooks and paper. So I have to issue him invoice #2. Where it shows the recent transaction and date. But I would also like to show his last transaction in invoice #2 with the date. Reminding him of how much he owns. Still he does not pay at this time.

 

In the main layout "customer info table" I want to pull up this customer by id and see how much he owes me and if he made any payments.

 

Then I can sent him a letter reminding him of his obligation. Or else I will break both his legs.

 

thanks

 

Mantral

[LingoJango]

Ok, getting back to your last request, since you have customer_id in your invoice table and the relationship between line items and invoices is working, I think you need the customer_id field to become a calculation field,

customer_id = invoices::customer_id. But you don't need that relationship to work at all: your links to the line items can all be done via invoices.

 

I would recommend another table, Payments, with PaymentID, PaymentDate and PaymentAmount as your fields (I would add PaymentCurrency but that's probably not relevant to you and complicates things greatly). You might want to add PaymentInvoicesPaid but that's going to complicate things.

 

Then, in the Customer table, add a BalanceDue calc field with the formula

BalanceDue = Sum(Invoices::InvoiceTotal ) - Sum (Payments::PaymentAmount)

 

(obviously you should use your own naming conventions, I'm just using mine here)

 

There is another option: you can link payments to invoices instead of linking them to customers; this way, it's easier to keep track of which invoices are closed, but it complicates matters when a customer pays for two invoices in one check and so on.