0

Can we link a field to a table view?

Hi, 

sorry to post here but i m working on a db and i dont know where to get the information that i need. I want to have a table with client information (id, name, phone number) for exemple.

I would like to have a table with all phone numbers and other table with the client linked with a phone number and with a date. So for exemple, john 514-555-5555 january 1st , john again with another number and another date....

then I would like to link the field 'phone number' in the client table to the most recent number for this client.

so if john change his number, I can have an historic of his past number... each number will be assign only once so I need to know if it s already assign but it s for the form part...

can we link field to table view? If so, how? if not, I can I do ?

Hope I explain it well! 

thank you and have a nice day!

33replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
    • Mconneen
    • Mconneen
    • 3 yrs ago
    • Reported - view

    U bet.. If first you do not succeed.. try .. try .. and try again.. :) 

    Like
  • ok..my current number show last number for each label...the problem is the field I created to get the max date...it seem not to separate the label....

     

    let t := this;
    t.dernier_changement = max((select 'Contact Phone')[number(Contact.ID) = number(Contact.ID) and text(Label) = text(t.Label)].dernier_changement)

     

    if I enter today s date for home and cell phone, it tell me yes for both but if I change Home for last week, it turn home number to ''no''

     

    so my view is ok but why it doesn t work previously with my field? I have created a formula field with that formula you gave me... What s wrong? Sorry to keep asking but not that simple to me! 

    Like
  • ok...I found what the problem is...

    Your formula is ok...except when I have only one entry... if I have 2 home number and 2 cell number...no problem...but if I have 1 home and 1 cell then it would give only the max of both...

    it should have a solution for this...I dont know why so it s hard to tell...

    What do you think?

    Like
  • other thing... if I only enter one number if my date is before may 7 it tell 'NO' but should be yes no matter the date...

    if I enter another one with different tag it s the same...

    hope it help to find out the issue!

    Like
    • Mconneen
    • Mconneen
    • 3 yrs ago
    • Reported - view

    On the first issue.. that was my fault... The formula was incorrect.. it should be as follows.. 

    theFormula

    Notice the "t.Contact.Id".... the prior formula did not have "t".. 

    As for the second.. Could you post the exact formula you are using?    Mine .. I made the simple assumption that the row modfied last (most recent) is the "current" phone number.. should be the same as doing the most recent "effective date".. if you are manually entering a date.  I guess make sure that the date field you are entering is actually of type date .. 

    Like
  • Thanks for your support! Works well now! Sound that with this new formula, the second problem were solve too!

    How can I make everything auto-updating when a new entry is made or a field change... I notice that if I change a date, the actual field change instantly but when I come back to the form, I see the old value still there...The table, view and form implicated in the changes are not updated automatically?

    thanks!

    Like
  • I have create a column in contact table to display Home active phone number. I would have to make a column for each label I suppose, but is there a way to link this field to the actual_number view in the form? Something like : match field name to label of actual_number view and show the number so I just have to name the field with the label I wish to show? 

    For now I tryed : if (concat(Phone.actuel) = true and concat(Phone.Label)) = "Maison" then
    Phone.Number
    end

     

    but nothing is shown...

    Like
  • Hi, 

    If I understand you right, you want to show the current phone number with the Label "Maison" in the data record of a contact. 

    For that please try the formula: 

     

    concat(Phone[text(Label) = "Maison"].Number)

     

    Kind Regards, Jörg

    Like
Like Follow
  • 3 yrs agoLast active
  • 33Replies
  • 8811Views