moving portal data to different database

[Benet]

hi'

i have a portal in my jobs database that lists sale items(line items table). i wish to be able to create invoices from the jobs db in a seperate invoice db. i can move the customer data (from the jobs table) no problem but when i try to move the portal data by means of a looping script (if...set field(jobs_db to field invoice_db)i only get the last line of the portal data. it is creating a record and then overwriting it with the next portal records data. so i only end up with one new record. how can i get the set field comand to realise it needs to write to a new record each time it loops through the portal lines in the jobs db ?. or is there another way round ?

thanks

Ben

[iMarcW]

Not sure if you are doing this already, but in your script (before the loop) you need to have a Go to Layout step that goes to a layout in the invoices database, one that's showing records from the table where line items will be stored *natively*, not as a portal or related item. Then, inside the loop, you need to create a new record before you have the set field step.

I hope you're just moving this data from one place to another and not making a duplicate copy of it, because the latter would be something you want to avoid. But that's another issue.

[Benet]

i am actually making a copy of the field data because i want the invoices to be fixed items where the jobs are more flexible.also it is required that via a checkbox only selected line items goto any related invoice.

that said im still a little confused. once my script is in the loop it has to be in the first (jobs line items) layout as it has goto record (next until last) steps that scan through the portal. i have been trying solutions as you explain but i find myself getting tied in knots trying to jump from one layout to another within the loop in order to read the portal lines and then write to the new layout/records.

does anyone have a example of this sort of thing working.

thanks again

ben

[comment]

Sounds like an import would be easier to handle.

P.S. I didn't get why you need to do this - in general, moving data is a vulnerable process and best avoided when other alternatives are available.

[Benet]

hmm.

i am new to this, but surely every time i add customer info to a job or stock info to a job or add stock from a purchase order into stock im moving data. if i always just have lookups or portals the data will change whenever the original data changes.

whenever i use set field a to fielb b im moving data?

how do imports work ?

thanks

ben

[comment]

surely every time i add customer info to a job or stock info to a job or add stock from a purchase order into stock im moving data.

Well, it depends on how you look at it. Usually, you would NOT "add customer info to a job". All you would add is the customer's ID, so that the job is related to a specific customer and has a "live" link to up-to-date customer's details.

In other situations you might need a snapshot of the related data, e.g. a lookup of product's price when creating an invoice. But I wouldn't call it "moving data", because in fact it is NEW data being created.

I don't understand well enough what you meant by:

i want the invoices to be fixed items where the jobs are more flexible.

to be able to tell in which category it falls.

[Benet]

i see,

your right of coarse, im taking snapshots in the invoices, thanks for sorting my terminology out, i dont have aproblem in the case of any of the job data i want copied except in the portal of line items, there again i need a snapshot of the portal data at that specific time. i need to recreate the info in the portal dependent on whether it it has its checkbox ticked (bolean 1/0). the script im using was orinally used to check stock in the supplied business solutions package. it seemed to be a good start as it scans portal data line by line and outputs a value. ive tried to mof=dify it to scan portal linesand output several values (fields) which it does. The problem arrises so far as it does not write each new line of data to a new record, hence i end up with one record containing the last scanned line only.

Im very up for a different approach but this way did seem to be a solution bar that small problem.

invoices need to be fixed while jobs are more flexible refers to this snapshot idea, several invoices or quotes or pro-formas may be created from one job that may change its form as time goes on (ie if quotes are accepted or the job grows or shrinks due to changing circunstances) the outputted invoices/quotes/pro formas, in my idea there are sepperate db's for jobs, invoices, pro formas and quotes, each of these is created as i said, with line items dependent on a checkbox ticked in the jobs db.

hope this clears thing up.

thanks for yor interest

ben

[comment]

Is it permissible in your solution to modify an item AFTER it has been checked and invoiced?

[Benet]

within the jobs db yes. once ive got the invoice bit sorted out the jobs records will hold another portal that lists related invoices/quotes ect so that you can always see what has actually been created and what it said at the time.

b

[comment]

Well, I don't know about that. Suppose an item has details of "Craftwork", "$100". Now it's checked and invoiced. The next day, we modify the price to "$120". What does that mean? Are you going to charge extra $20? You don't have a record for $20.

Anyway, if that's how you want to do it, I see two options:

1. Find the records you want to invoice in the sale items table. Go to a layout of the target table (invoices? invoice line items?), and import records from the sale items table.

You'll need to figure out (a) how not to import the same record twice, and ( how to create invoices from invoice line items (if that's how it's supposed to work). You haven't supplied enough details for us to advise you on these.

2. Define a relationship between sale items and the target table, based on SaleItemID. Allow creation of records on the target table side. Have your script loop between the sale item records and set the related target fields to the values in the current record.

[Benet]

thanks for the advice. ill try to put it into practice tommorrow and let you know how i get on.

[Benet]

thanks comment,

i gave solution 2 a quick try and it worked. i susspected there was a definition problem with my dbs, i had the "line items" and "invoice line items" defined by the JobID so i guess if they all had the same JobID no new records would be created. Its great when you see the logic working. Ive been tearing my hair out for 4 days over that.

Incidentally the jobs db is really only for roughly putting together jobs or potential jobs. the invoices and quotes are where the figures matter and they will be the reference to real work to be done or having been done.

i see what you mean about price rises but if we always refer to quotes and invoices for the "real" prices given i think it should be ok.

thanks very much anyway. its been a huge relief.

B

[Cortical]

The initial portal issue sounds like a refresh problem; the script leaves the portal and does not know where to re-enter; so use a counter set $_n =1 before the loop, and set $_n =$_n+1 in the loop to dictate the portal row.

If you have more than one portal on the layout, stacking becomes an issue. Hive the portal an object name, and use go to object (name) in the loop.

The snapshot capture of the Job Line Items; I was doing this recently; In this case I use variables to capture the required values, go layout Invoice, new record, hard code the data from the variables...

The alternate is to use a acrr rel and revalue the key with the line itemID in the loop.

One could also use the JobLineItems table as the InvoiceLIneItems table, by adding an InvoiceID field, and also ensuring the JLI records were locked once an Invoice state had been applied.

[Benet]

the relationship works. but again there is a small problem. if the job line item id is the same for two created invoices the script edits the the record with the same job line item id. hence stealing the line item from one invoice and putting it in another (because the invoices are related to invoice line items by invoice iD) can i add a second relationship to the "Job line items ID" = "job Line Items ID" im using between layouts "invoice line items" and "job line items" so that this does not happen ?

thanks again

B

[comment]

I am afraid you have lost me completely. You need to draw a clear picture of your tables and their relationships. In my suggested method #2, the source table (sale items?) has a UNIQUE ID field which I called SaleItemID, so the relationship to the target table is one-to-one.

[Benet]

sorry, i was being a bit vague, i managed to find a definition that worked. i added invoice id=temp invoice id (as wel as putting a "temp invoice id" field into the job line items and filling it whenevr a new invoice is created) to the relationship you suggested so new records are created whenever both these criteria are met. as aposed to new records not being created because "job line items id" allready exists ?

Well it does work.

I have to say the biggest problem i have is cleaning up the mess ive made on my way to finding the solution that ultimately works.

I read the white paper and am trying to implement is very sound advise, but i stil get carried away and forget what ive done. back peddaling is no fun !

I think i may be suffering from many typos, not a good trait in scripting!!

thanks again

you have been a great help.

B

[Benet]

ensu

One could also use the JobLineItems table as the InvoiceLIneItems table, by adding an InvoiceID field, and also ensuring the JLI records were locked once an Invoice state had been applied.

thanks cortical, some of your advice is alittle advanced for me, however i i like the logic behind this idea.

Just one thing, how do you "ensure the JLI records are locked once an Invoice state had been applied"

Thanks

B