Source language: Translate to:

Updating Linked Fields

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

Updating Linked Fields

Postby BRobinsonS » Mon Oct 22, 2012 5:32 pm

What is a good technique to update linked field in a Master/Detail relationship?
My app is a membership program. Main Table is Members. Second Table is DuesPaid table.
I link them by common field MemNum.

Right now when I go to the Payments screen I have a button to Add Payment which copies the MemNum into the Payments table so link works.
And then add in Date (Paid), Amount, ReceiptNo.

My current problem is that a temporary membership number is used for the payment.
Later a permanent MemNum is received from Head Office.
So I have to go back to the Member record and change the MemNum in Main Table but then I lose the 'link' because the temporary MemNum is still in the DuesPaid table and I lose the Master/Detail Link.

Is there a way to update in Main Table and the MemNum is also updated in Second Table?
Brian Robinson
'When all else fails, try again!'
www.ComputerSoftwareSystems.com
User avatar
BRobinsonS
 
Posts: 337
Joined: Sun Sep 25, 2005 9:00 pm
Location: Ontario, Canada

Re: Updating Linked Fields

Postby Gaev » Mon Oct 22, 2012 7:34 pm

Brian:

1) It is not good design practice to link tables using fields that will change values ... generally, you use the unique (AutoInc) field of the parent table as the foreign key in the child table.

2) why do you need to assign temporary MemNum ... what is so special that it needs to be assigned at Head Office ? ... why not keep the 'next available Memnum' value in a separate Table (with one record and one field) ?

3) if you have to assign a new MemNum ...

- save the temporary MemNum in a variable

- update the Member table with the new value

- query just the DuesPaid table for the temporary MemNum value ... loop through the records ... replace the MemNum during each iteration
User avatar
Gaev
 
Posts: 3717
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: Updating Linked Fields

Postby BRobinsonS » Thu Oct 25, 2012 6:30 am

It is not good design practice to link tables using fields that will change values

This change is a one time change.
why do you need to assign temporary MemNum ... what is so special that it needs to be assigned at Head Office ?

This is a Legion Membership app. In order to process the 'new' member at the Branch I assign a temporary number.
When we get the permanent membership number with membership card from 'Command Office', then I wanted to replace the temporary number with the permanent one.
Again the hiccup is changing the DuesPaid.MemNum when I change the Contacts.MemNum (Members.MemNum) so I don't lose the link.
generally, you use the unique (AutoInc) field of the parent table as the foreign key in the child table.

In hind sight, I guess I could have used a different field to link with.
Would I copy the AutoInc field of the parent table to the child table when I create a new record in the child table (DuesPaid)?
It would be unique right? Then the relationship would be defined using the AutoInc field in Parent table with the field in the child table which would be of type Integer?

query just the DuesPaid table for the temporary MemNum value ... loop through the records ... replace the MemNum during each iteration

I will be trying this soon. I have done this in another app (not Neobook/NeobookDBPro).
Or, since I still have control of the app and data I could adjust the app using different 'link' fields.
Brian Robinson
'When all else fails, try again!'
www.ComputerSoftwareSystems.com
User avatar
BRobinsonS
 
Posts: 337
Joined: Sun Sep 25, 2005 9:00 pm
Location: Ontario, Canada

Re: Updating Linked Fields

Postby Gaev » Thu Oct 25, 2012 7:58 am

BrianS:
Would I copy the AutoInc field of the parent table to the child table when I create a new record in the child table (DuesPaid)?
Yes.
It would be unique right?
Yes, the Database software takes care of that ... note that Dave has often advised (on this forum) that a number of issues with MS Access are avoided if the Table contains such a field (whether you need it or not) ... and (I say) sooner or later you will need it ... so best to have it in every record definition.
Then the relationship would be defined using the AutoInc field in Parent table with the field in the child table which would be of type Integer?
Yes
I will be trying this soon. I have done this in another app (not Neobook/NeobookDBPro).

Code: Select all
dbpOpenTable "yourDB" "Members" ""
dbpOpenTable "yourDB" "DuesPaid" ""
... say variables curMemNum and newMemNumber contain the current and new membership numbers
dbpQuery "yourDB" "Members" "MemNumber = curMemNum"
If "[yourDB.Members.$RecCount]" ">" "!0"
    GoSub "ReNumberMemberRecords"
    dbpQuery "yourDB" "DuesPaid" "MemNumber = curMemNum"
    If "[yourDB.DuesPaid.$RecCount]" ">" "!0"
        GoSub "ReNumberDuesPaidRecords"
    Else
        AlertBox "Not Found" "DuesPaid record with [curMemNum] not found"
    EndIf   
Else
    AlertBox "Not Found" "Members record with [curMemNum] not found"
EndIf



:ReNumberMemberRecords
SetVar "[LoopCount]" "[yourDB.Members.$RecCount]"
dbpFirst "yourDB" "Members"
Loop "1" "[LoopCount] "[junk]"
   SetVar "[yourDB.Members.MemNum]" "[newMemNumber]"
   dbpNext "yourDB" "Members"
EndLoop
AlertBox "Info" "[yourDB.Members.$RecCount] record(s) in Members Table renamed from [curMemNumber] to [newMemNumber]"
Return

:ReNumberDuesPaidRecords
SetVar "[LoopCount]" "[yourDB.DuesPaid.$RecCount]"
dbpFirst "yourDB" "DuesPaid"
Loop "1" "[LoopCount] "[junk]"
   SetVar "[yourDB.DuesPaid.MemNum]" "[newMemNumber]"
   dbpNext "yourDB" "DuesPaid"
EndLoop
AlertBox "Info" "[yourDB.DuesPaid.$RecCount] record(s) in DuesPaid Table renamed from [curMemNumber] to [newMemNumber]"
Return
User avatar
Gaev
 
Posts: 3717
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: Updating Linked Fields

Postby BRobinsonS » Thu Oct 25, 2012 4:35 pm

Wow!
Thank you. Gaev for the code. :D

Question: Do I need to cancel the relationship to Query the tables and re-set the relationship?

Can't remember what ! in "!0" does.
Brian Robinson
'When all else fails, try again!'
www.ComputerSoftwareSystems.com
User avatar
BRobinsonS
 
Posts: 337
Joined: Sun Sep 25, 2005 9:00 pm
Location: Ontario, Canada

Re: Updating Linked Fields

Postby Gaev » Fri Oct 26, 2012 6:29 am

BrianS:
Question: Do I need to cancel the relationship to Query the tables and re-set the relationship?

You can undo a previous dbpQuery by ...
Code: Select all
dbpShowAll "yourDB" "yourTable"

Can't remember what ! in "!0" does.
I usually place an ! when in doubt about what NeoBook will do with the values that follow ... the ! tells NeoBook to use the values 'as specified' ... i.e. without doing any kind of math on them.
User avatar
Gaev
 
Posts: 3717
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: Updating Linked Fields

Postby BRobinsonS » Fri Oct 26, 2012 7:07 am

I know about
dbpShowAll "yourDB" "yourTable".


But do I use dbpEndRelationship before doing the queries on the Parent and Child table or does it matter?
Brian Robinson
'When all else fails, try again!'
www.ComputerSoftwareSystems.com
User avatar
BRobinsonS
 
Posts: 337
Joined: Sun Sep 25, 2005 9:00 pm
Location: Ontario, Canada

Re: Updating Linked Fields

Postby Gaev » Fri Oct 26, 2012 7:48 am

brianS:
But do I use dbpEndRelationship before doing the queries on the Parent and Child table or does it matter?

I have not used the dbpDefineRelationship command of the plug-in ... my solution was to just 'change the MemNum field values in all matching records in both Tables separately ... so if you don't need the Relationship elsewhere in your Application, don't define it ... otherwise ...

- end the relationship
- do the changes to the memNum field
- redefine the relationship
User avatar
Gaev
 
Posts: 3717
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada


Return to NeoBookDBPro

Who is online

Users browsing this forum: No registered users and 1 guest

cron