How to import selected records from a related file into a portal?

[nareyramos]

Hi guys. I have two ralated databases named Invoices and Products. The Products database has a list of different types of products but only some of them are new. My Invoices database has a portal to show the products that I am going to bill under each invoice. Now, sometimes I have to include in an invoice ALL the products that are new, and I want to import them all at the same time by clicking a button instead of selecting them one by one in my portal. How can I do this? Thank you.

[bcooney]

You're missing a table, aren't you? Don't you need an InvoiceLineItems table - the join table between Invoices and Products?

 

How do you tag a product as "new"? Import from where?

[nareyramos]

I am sorry. Yes, I do have a Line Items table under my Invoices database. As to how I tag a product as "new", I have a field in my Products database that is a checkbox set with one value (new) that I check when the product is new. Let's say that I have a total of 200 products (new and not new) in my Products database and I have a guy that wants to buy all only 50 products that are new. In a case like this, I open my Invoices database to create an invoice, but I have to select in my portal each product tagged as "new" one by one which is very time consuming. I would like to have a button in my Invoices database that allows me to import from the Products database all the products tagged as "new" with one click.

 

Thanks, NR

Correction: "... I have a guy that wants to buy all only 50 products that are new."

[bcooney]

One Method --

 

Button "Add Products tagged as New" above LineItems portal performs script:

 

$InvoiceID = Invoice::ID //capture parent invoice ID

go to layout "Products"

Find all Products where checkbox is selected (flag_New =1)

Export to temp directory temp file all IDs

go to layout InvoiceLineItems

import temp file with ID mapped to LineItems foreign key InvLI::_kF_ProductID

Replace InvLI::_kF_InvoiceID "$InvoiceID" //populate InvoiceID foreign key

go to layout Invoice

 

So, when do you "unmark" product as New? You may want to reference a date field in Products instead, and use that in your find.

[nareyramos]

Moderator, please forgive my ignorance but I don't know how to create a script with that information, and the questions will be way too many. If it is not too much to ask, it would be easier for me if you can show me through an example. I can email you a sample database and perhaps you could send it back to me with a script. That way I could analyze the script and learn for the future. I tried to upload the files but the site doesn't let me. If you send me a message to [email protected] I will email you the batabase. Thanks.

[bcooney]

I'll find some time to upload a demo. Sorry, didn't mean to be unhelpful.

 

OK, demo attached. This is just one method.

NewProductsDemo.fp7.zip

[nareyramos]

Thank you very much. I just checked it and saw how it works. I will work on my file tomorrow and will let you know. Thanks again. I appreciate your help.

[bcooney]

You're welcome. Once you are comfortable with the technique, I suggest thinking about the definition of "new." Can it be a calc based on a date? That would eliminate the need to manual maintain the flag.