[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.axapta.programming

Relations (Part of Misc Questions

G

10/31/2005 10:33:00 AM

I have placed this question earlier but I was not clear so I did not get an
answer.
Say I have 2 tables with m-m realation employee and pay item where employee
associated with many pay items and pay item associated with many employees.
This m-m relationship is broken to 3rd table called employee_payitem.
How does this relationships drawn in the following nodes
- Relations in employee table node
- Relations in pay item table node
- Relations in employee pay item node
- I have a form that reads from the 3 tables. How to set data sources for form
- I have a report that reads from the 3 tables. How to set data sources for
report
- I have a query that reads from the 3 tables. How to set data sources for
query
---------------------
Thank You and Best Regards
6 Answers

vladimir40

11/3/2005 12:18:00 AM

0

Here is the example:
EmplTable(emplid,name..) emplid is PK
PayItem(payItemid,name,amount..) payItemId is PK
Epml_payitem(emplid,payItemid,startDate...) emplid&payitemid is PK;
Make payItemid EDT with relation to PayItem table.
What is driving your data input form?
1. Employee. Make a form main DS EmplTable and Empl_payItem connected to
EmpllTable
2. PayItem. main DS PayItem and Empl_payItem connected to PayItem.

What do you see on the report?
1. employee payrates: the same #1 from form
2. payrate employees: the sames #2

Do you need read 3 tables? How is driving?
1. Employee: EmplTable->Empl_PayRate->PayRate
2.PayRate: PayRate->Empl_PayRate->EmplTable

"G" wrote:

> I have placed this question earlier but I was not clear so I did not get an
> answer.
> Say I have 2 tables with m-m realation employee and pay item where employee
> associated with many pay items and pay item associated with many employees.
> This m-m relationship is broken to 3rd table called employee_payitem.
> How does this relationships drawn in the following nodes
> - Relations in employee table node
> - Relations in pay item table node
> - Relations in employee pay item node
> - I have a form that reads from the 3 tables. How to set data sources for form
> - I have a report that reads from the 3 tables. How to set data sources for
> report
> - I have a query that reads from the 3 tables. How to set data sources for
> query
> ---------------------
> Thank You and Best Regards

Luegisdorf

11/3/2005 3:43:00 PM

0

Hi G

you have to create the relations on your normalized table called
"employee_payitem". But If you use Extended Data Types (f.ex EmplId) which
has already defined relations so you don''t need to define once more.

In the form I would do it like in inventtable

innerjoin on employee to employee_payitem
innerjoin on payitem to employee_payitem
no Join Source for employee_payitem (thats the master-table)

this should work. Feel free to ask more questions if I''m going wrong or
there are any other questions.

Best regards
Patrick

"G" wrote:

> I have placed this question earlier but I was not clear so I did not get an
> answer.
> Say I have 2 tables with m-m realation employee and pay item where employee
> associated with many pay items and pay item associated with many employees.
> This m-m relationship is broken to 3rd table called employee_payitem.
> How does this relationships drawn in the following nodes
> - Relations in employee table node
> - Relations in pay item table node
> - Relations in employee pay item node
> - I have a form that reads from the 3 tables. How to set data sources for form
> - I have a report that reads from the 3 tables. How to set data sources for
> report
> - I have a query that reads from the 3 tables. How to set data sources for
> query
> ---------------------
> Thank You and Best Regards

richard

8/1/2008 10:13:00 PM

0

Richard,

Is is possible to get the lastLogon field using this method?
I've adapted the script from your site and can now get the mail, but not the lastLogon field which
I've seen used elsewhere. It always gives an error 'Object doesn't support this property or
method', even though I know that some of the users have certainly logged in.

The key bits of the script, as far as I know, are:

strAttributes = "sAMAccountName,cn,mail,lastLogon"
.
.
.

' Write the resulting recordset to file
Do Until adoRecordset.EOF
' Retrieve values and display.
strName = adoRecordset.Fields("sAMAccountName").Value
strCN = adoRecordset.Fields("cn").value
strMail= adoRecordset.Fields("mail").value

On error resume Next
strLastLogon= adoRecordset.Fields("lastLogon").value
if Err<>0 then
sLastLogonErrorMsg=replace(replace(Err.description,vbCr,""),vbLf,"")
else
sLastLogonErrorMsg="ok"
end if
Err.clear
On error goto 0

OutputLine = strName & ";" & strCN & ";" & strMail & ";" & strLastLogon & ";" &
sLastLogonErrorMsg
Wscript.Echo OutputLine
objOutputFile.WriteLine(OutputLine )
adoRecordset.MoveNext
Loop


As I mentioned, if I don't try to get the lastLogon, everything works.

Thanks for any help,
Richard

Richard Mueller [MVP]

8/2/2008 12:42:00 AM

0

Richard wrote:

> Is is possible to get the lastLogon field using this method?
> I've adapted the script from your site and can now get the mail, but not
> the lastLogon field which
> I've seen used elsewhere. It always gives an error 'Object doesn't
> support this property or
> method', even though I know that some of the users have certainly logged
> in.
>
> The key bits of the script, as far as I know, are:
>
> strAttributes = "sAMAccountName,cn,mail,lastLogon"
> .
> .
> .
>
> ' Write the resulting recordset to file
> Do Until adoRecordset.EOF
> ' Retrieve values and display.
> strName = adoRecordset.Fields("sAMAccountName").Value
> strCN = adoRecordset.Fields("cn").value
> strMail= adoRecordset.Fields("mail").value
>
> On error resume Next
> strLastLogon= adoRecordset.Fields("lastLogon").value
> if Err<>0 then
> sLastLogonErrorMsg=replace(replace(Err.description,vbCr,""),vbLf,"")
> else
> sLastLogonErrorMsg="ok"
> end if
> Err.clear
> On error goto 0
>
> OutputLine = strName & ";" & strCN & ";" & strMail & ";" & strLastLogon
> & ";" &
> sLastLogonErrorMsg
> Wscript.Echo OutputLine
> objOutputFile.WriteLine(OutputLine )
> adoRecordset.MoveNext
> Loop
>
>
> As I mentioned, if I don't try to get the lastLogon, everything works.
>
> Thanks for any help,
> Richard

The lastLogon attribute is Integer8, a large (64-bit) number representing a
date. It cannot be handled the way most string attributes are handled. In
fact, something called the IADsLargeInteger interface must be used, that
treats the value similar to an object and exposes HighPart and LowPart
methods that break the 64-bit value into two 32-bit values. This is
explained in detail at this link:

http://www.rlm.../Integer8Attr...

However, another complication is that lastLogon is not replicated. This
means that a different value is saved on every Domain Controller (DC). When
the user authenticates to a DC, the lastLogon attribute for that user is
updated on that DC, but the value is not replicated. To determine the true
lastLogon date/time you must query every DC in the domain. I have an example
VBScript program that retrieves the last logon dates for all users in the
domain by querying every DC linked here:

http://www.rlm.../Last%2...

As explained in the link, there is a new attribute called lastLogonTimeStamp
that is available if your domain is at Windows 2003 functional level. This
attribute is replicated, so you only need one query to get the value, but it
is only updated if the old value is at least 14 days (by default) in the
past. This is so you can find unused accounts. Replicating any value
everytime a user logged on would increase replication traffic unacceptably.

The code to retrieve lastLogonTimeStamp would be similar to (in part):
=============
' Obtain local Time Zone bias from machine registry.
Set objShell = CreateObject("Wscript.Shell")
lngBiasKey = objShell.RegRead("HKLM\System\CurrentControlSet\Control\" _
& "TimeZoneInformation\ActiveTimeBias")
If (UCase(TypeName(lngBiasKey)) = "LONG") Then
lngTZBias = lngBiasKey
ElseIf (UCase(TypeName(lngBiasKey)) = "VARIANT()") Then
lngTZBias = 0
For k = 0 To UBound(lngBiasKey)
lngTZBias = lngTZBias + (lngBiasKey(k) * 256^k)
Next
End If
' ...

strAttributes = "sAMAccountName,cn,mail,lastLogonTimeStamp"
' ...

Do Until adoRecordset.EOF
' Retrieve values and display.
strName = adoRecordset.Fields("sAMAccountName").Value
strCN = adoRecordset.Fields("cn").value
strMail= adoRecordset.Fields("mail").value

' Convert Integer8 value to date/time in current time zone.
On Error Resume Next
Set objDate = adoRecordset.Fields("lastLogonTimeStamp").Value
If (Err.Number <> 0) Then
On Error GoTo 0
dtmDate = #1/1/1601#
Else
On Error GoTo 0
lngHigh = objDate.HighPart
lngLow = objDate.LowPart
If (lngLow < 0) Then
lngHigh = lngHigh + 1
End If
If (lngHigh = 0) And (lngLow = 0 ) Then
dtmDate = #1/1/1601#
Else
dtmDate = #1/1/1601# + (((lngHigh * (2 ^ 32)) _
+ lngLow)/600000000 - lngBias)/1440
End If
End If

OutputLine = strName & ";" & strCN & ";" & strMail & ";" & dtmDate
Wscript.Echo OutputLine
objOutputFile.WriteLine(OutputLine )
adoRecordset.MoveNext
Loop
===========
I did not Dim the new variables. The same code can be used to retrieve
lastLogon (instead of lastLogonTimeStamp), but the value will only be valid
if you have one DC (or are lucky enough by chance to query the DC with the
largest value in the domain). Otherwise, especially if your domain is not at
W2k3 functional level, best would be to use the code to retrieve lastLogon
for all users in the domain (linked above) in a separate operation.

--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlm...
--


Al Dunbar

8/2/2008 5:07:00 PM

0


"Richard Mueller [MVP]" <rlmueller-nospam@ameritech.nospam.net> wrote in
message news:u7LCiiD9IHA.3336@TK2MSFTNGP03.phx.gbl...
> Richard wrote:
>
>> Is is possible to get the lastLogon field using this method?
>> I've adapted the script from your site and can now get the mail, but not
>> the lastLogon field which
>> I've seen used elsewhere. It always gives an error 'Object doesn't
>> support this property or
>> method', even though I know that some of the users have certainly logged
>> in.
>>
>> The key bits of the script, as far as I know, are:
>>
>> strAttributes = "sAMAccountName,cn,mail,lastLogon"
>> .
>> .
>> .
>>
>> ' Write the resulting recordset to file
>> Do Until adoRecordset.EOF
>> ' Retrieve values and display.
>> strName = adoRecordset.Fields("sAMAccountName").Value
>> strCN = adoRecordset.Fields("cn").value
>> strMail= adoRecordset.Fields("mail").value
>>
>> On error resume Next
>> strLastLogon= adoRecordset.Fields("lastLogon").value
>> if Err<>0 then
>> sLastLogonErrorMsg=replace(replace(Err.description,vbCr,""),vbLf,"")
>> else
>> sLastLogonErrorMsg="ok"
>> end if
>> Err.clear
>> On error goto 0
>>
>> OutputLine = strName & ";" & strCN & ";" & strMail & ";" &
>> strLastLogon & ";" &
>> sLastLogonErrorMsg
>> Wscript.Echo OutputLine
>> objOutputFile.WriteLine(OutputLine )
>> adoRecordset.MoveNext
>> Loop
>>
>>
>> As I mentioned, if I don't try to get the lastLogon, everything works.
>>
>> Thanks for any help,
>> Richard
>
> The lastLogon attribute is Integer8, a large (64-bit) number representing
> a date. It cannot be handled the way most string attributes are handled.
> In fact, something called the IADsLargeInteger interface must be used,
> that treats the value similar to an object and exposes HighPart and
> LowPart methods that break the 64-bit value into two 32-bit values. This
> is explained in detail at this link:
>
> http://www.rlm.../Integer8Attr...
>
> However, another complication is that lastLogon is not replicated. This
> means that a different value is saved on every Domain Controller (DC).

Strictly speaking, this is not exactly true (sorry, Richard). If the user
has never authenticated against two or more DC's, then these would all
register the same value for lastlogon. Of course, this would be done by
having no LastLogon attribute value stored for that account on these
particular DC's.

/Al

> When the user authenticates to a DC, the lastLogon attribute for that
> user is updated on that DC, but the value is not replicated. To determine
> the true lastLogon date/time you must query every DC in the domain. I have
> an example VBScript program that retrieves the last logon dates for all
> users in the domain by querying every DC linked here:
>
> http://www.rlm.../Last%2...
>
> As explained in the link, there is a new attribute called
> lastLogonTimeStamp that is available if your domain is at Windows 2003
> functional level. This attribute is replicated, so you only need one query
> to get the value, but it is only updated if the old value is at least 14
> days (by default) in the past. This is so you can find unused accounts.
> Replicating any value everytime a user logged on would increase
> replication traffic unacceptably.
>
> The code to retrieve lastLogonTimeStamp would be similar to (in part):
> =============
> ' Obtain local Time Zone bias from machine registry.
> Set objShell = CreateObject("Wscript.Shell")
> lngBiasKey = objShell.RegRead("HKLM\System\CurrentControlSet\Control\" _
> & "TimeZoneInformation\ActiveTimeBias")
> If (UCase(TypeName(lngBiasKey)) = "LONG") Then
> lngTZBias = lngBiasKey
> ElseIf (UCase(TypeName(lngBiasKey)) = "VARIANT()") Then
> lngTZBias = 0
> For k = 0 To UBound(lngBiasKey)
> lngTZBias = lngTZBias + (lngBiasKey(k) * 256^k)
> Next
> End If
> ' ...
>
> strAttributes = "sAMAccountName,cn,mail,lastLogonTimeStamp"
> ' ...
>
> Do Until adoRecordset.EOF
> ' Retrieve values and display.
> strName = adoRecordset.Fields("sAMAccountName").Value
> strCN = adoRecordset.Fields("cn").value
> strMail= adoRecordset.Fields("mail").value
>
> ' Convert Integer8 value to date/time in current time zone.
> On Error Resume Next
> Set objDate = adoRecordset.Fields("lastLogonTimeStamp").Value
> If (Err.Number <> 0) Then
> On Error GoTo 0
> dtmDate = #1/1/1601#
> Else
> On Error GoTo 0
> lngHigh = objDate.HighPart
> lngLow = objDate.LowPart
> If (lngLow < 0) Then
> lngHigh = lngHigh + 1
> End If
> If (lngHigh = 0) And (lngLow = 0 ) Then
> dtmDate = #1/1/1601#
> Else
> dtmDate = #1/1/1601# + (((lngHigh * (2 ^ 32)) _
> + lngLow)/600000000 - lngBias)/1440
> End If
> End If
>
> OutputLine = strName & ";" & strCN & ";" & strMail & ";" & dtmDate
> Wscript.Echo OutputLine
> objOutputFile.WriteLine(OutputLine )
> adoRecordset.MoveNext
> Loop
> ===========
> I did not Dim the new variables. The same code can be used to retrieve
> lastLogon (instead of lastLogonTimeStamp), but the value will only be
> valid if you have one DC (or are lucky enough by chance to query the DC
> with the largest value in the domain). Otherwise, especially if your
> domain is not at W2k3 functional level, best would be to use the code to
> retrieve lastLogon for all users in the domain (linked above) in a
> separate operation.
>
> --
> Richard Mueller
> MVP Directory Services
> Hilltop Lab - http://www.rlm...
> --
>
>


richard

8/2/2008 8:24:00 PM

0

On Fri, 1 Aug 2008 19:42:17 -0500, "Richard Mueller [MVP]" <rlmueller-nospam@ameritech.nospam.net>
wrote:

<---- snip ---->

Richard,

Thank you again for your help with these issues. I now have this part of my code doing what I need.

Regards,
Richard