vlookup like command help required in vfp9 1

[ieesh]

sir please help me require


I have 4 files with same field ie bill_no but I first want to start step by step
so first with two files which I have same bill number
the first file is ledger
1 bill number is divided into 4/5 parts where same bill number has 4 /5 rows dividing into different heads
I want to add 1 more field say vch_no such that same bill number has same vcc_no and when bill_no changes vch_no is added +1 and so on
also no bilnumber can have same vch_no as above
now when this is done there is another dbf bill file which also has same bill_no
here also I want vch_no to be added but it should have the same vch_no as was in the ledger field
as the records are more than 85000 exporting and then doing lookup and so on is very lengthy procedure can we have some commands to help me and this involves 1 by 1 to 4 files

please help
require help desperately

 

[Mike Lewis]

I appreciate that this is your first post in this forum. But it is really a bad idea to ask two completely separate questions in the same post. Not only does it lead to a complicated hard-to-follow thread, but it is unhelpful for anyone who has a similar problem and who is searching for a solution.

Please edit your post (click the Edit button in the bottom right corner). Remove one of your two questions, and then paste it into a separate post in a new thread.

It would also be helpful if you edit the title of the post to reflect more accurately the problem. Again, this is for the benefit of other people searching for a similar problem.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[ieesh]

THANKS DONE SORRY MY FIRST POST

 

[Olaf Doschke]

Your description is not very clear to me, so let me rephrase to see, if I understand it correctly:

You have 4 DBFs which all have data about bills and a common field bill number. There are 4-5 records for each bill, ie several, it's not a primary key for the table, but maybe for one of them, the ledger it is and for other tables it's a foreign key. If not, look into understanding the concept of primary and foreign keys.

You want to introduce a new field vch_no that will be a sequence, but only incrementing when the bill_no changes.

What you're not saying but can be concluded is, that bill_no itself is no such sequence number, or you'd be done already.

Depending on having a good normalized form of data, ie one main table - maybe your ledger table - with only 1 record per bill_no, you'd have a main table where it originates, is unique and then is used in the other table to reference belonging to the same bill.

Then it's as simple as adding in integer autotinc field to the main table that has 1,2,3,4... and then use that to update the other 3 tables with that number.

If you don't have one main table, then you should definitely reorganize your 4 tables and maybe have a 5th exactly to store all head data of bills including the vch_no as sequence and unique bill numbers.

Bye, Olaf.


Olaf Doschke Software Engineering

https://www.doschke.name

 

[ieesh]

thank you sir for your reply I am attaching 3 files I will explain
first file is say data 2 and bill2
now bill not with doc_type="SALE" is same in both but data2 has 3 or 4 rows for same numbers ie bill2.bill_no=data2.bill_no for doc_type="SALE"
want to replace voucher_no with say 11001, +1, +1 as each bill_no keeps on changing for doc_type="SALE". when this is done
want the same number to appear in bill2.vch_no for same bill_no for type="SALE"
and no vch_no can be same
same way we will do in third file for doc_type="SALE" where ledger.bill_no=data2.bill_no for doc_type="SALE"

if you can help 1 more help
now in ledger.dbf for doc_type="VOUCHER" the problem is there are 2 lines for say toe="CB"
where 1 or more acd (which is master code) is credit and 1 or more is debit for the same bill_no
now in this first we want to add vch_no again 14001, +1, +1 as each bill_no changes and no two can be same
when this is done I want
I want to copy to another new file abcd.dbf such that we get acd of 1 debit to go in credit of same vch_no into subacd
so suppose we have bill_no. date. amount. drcr. acd. subacd. vch_no
12345. 03/03/2020. 50000. D. 56. 890
12345. 03/03/2020 50000. C. 896. 890

WE SHOULD GET. 12345. 03/03/2020. 50000. D. 56. 896. 890
APPRECIATE HELP