0

How can I populate a Bill To address from Job address if both the same?

I have two tables.

1. Customer Information

2. Invoice Information

The Customer Information has a 'Customer/Job' Address (several fields).

The Invoice Information has a 'Bill To' Address (corresponding number and format to 'Customer/Job' Address.

I have a Yes/No option for Is Billing Address the same as the Job Address.

What I want is for the Billing Address to auto-populate from the Job address if both are the same.

Can someone please explain to me how to do this such as should each corresponding field be a formula If type else or some other way? Thanks in advance for any help.

See attached form for what it looks like. 

Screen Shot 2020-06-10 at 17.01.05

13 replies

null
    • Choices_Software_Dean
    • 3 yrs ago
    • Reported - view

    In invoice table go to Edit Fields and from the right column drag the Customer table reference over to the center column. Change the name of your choice field to Choices. Create a formular field with:

     

    if text(Choices) = "Yes" then
     Customers.'Customer/Job'
    end

     

    Then go back and rename your Choices field to whatever you want. I do not have time to test this right now, but hopefully it will get you started.

    • Choices_Software_Dean
    • 3 yrs ago
    • Reported - view

    In code, field names that contain a space must be surrounded by single quotes. Double quotes are used for static text. For this reason you may want to stay away from using quotes within field names.

    • PAGE
    • Bill_Page
    • 3 yrs ago
    • Reported - view

    Hi Dean, thank you for this very much. Unfortunately, I am not adjusting to Ninox very well and am feeling a bit dumb to say the least. FMP seemed so much easier back in the day. 

    What I wanted was for when the Bill To address was the same as the Job address (from the customer record) then the Title, Name, Address would all auto populate from the customer record into the invoice record matching fields.

    I put the formula below in the field properties for the Yes/No switch, which I thought would trigger the formula.
    See screenshots if it helps.

    If 'Is Bill To Address the same as Job Address' = "Yes" then
    'Title' = 'Customer Data'.'Title'

    end

     

    Screen Shot 2020-06-17 at 18.12.42

    Screen Shot 2020-06-17 at 18.12.42

    Screen Shot 2020-06-17 at 17.59.17

    Anyway, any help is really appreciated before I give up and go back to something more familiar.

    • PAGE
    • Bill_Page
    • 3 yrs ago
    • Reported - view

    Oops, wishe we could edit posts and this tiny area is difficult to see what is in it. Same images postes twice by mistake.

    • PAGE
    • Bill_Page
    • 3 yrs ago
    • Reported - view

    It that experimental formula worked I was then going to add all the other fields such as name, address etc.

    Still trying to work it out though.

    • PAGE
    • Bill_Page
    • 3 yrs ago
    • Reported - view

    Here is what is looks like in the Field Trigger of the field 'Is Bill To Address the same as Job Address'

    For some reason the as is in purple the same as the if, then, end.

    Screen Shot 2020-06-17 at 19.13.38

    • PAGE
    • Bill_Page
    • 3 yrs ago
    • Reported - view

    Some more info on what I am trying to do...

    I was thinking the formula on trigger of the Yes/No switch would look something like this.

    if 'Is Bill To Address the same as Job Address' = "Yes" then
    Title = 'Originating Customer Name'.Title
    if 'Is Bill To Address the same as Job Address' = "Yes" then
    'First Name' = 'Originating Customer Name'.'First Name'
    if 'Is Bill To Address the same as Job Address' = "Yes" then
    'Family Name' = 'Originating Customer Name'.'Family Name'
    if 'Is Bill To Address the same as Job Address' = "Yes" then
    'Telephone Type (Main)' = 'Originating Customer Name'. 'Telephone Type (Main)'
    end

    And so on until all fields are copied across/match.

    • PAGE
    • Bill_Page
    • 3 yrs ago
    • Reported - view

    Still needing help on this guys, if anyone can figure it out - thanks!

    • Nick
    • 3 yrs ago
    • Reported - view

    Try it this way
    ---

    if 'Is Bill To Address the same as Job Address' = true then
    Title := 'Originating Customer Name'.Title;
    'First Name' := 'Originating Customer Name'.'First Name';
    'Family Name' := 'Originating Customer Name'.'Family Name';
    Phone := 'Originating Customer Name'. 'Telephone Type (Main)'
    end

    ---

    • PAGE
    • Bill_Page
    • 3 yrs ago
    • Reported - view

    Thanks Nick, the following works great except when I then click the Yes/No back to No, the contents do not reset to being blank again. I am going to play with what you have posted and here is my slightly modified version (field name for phone) that is working one way. 

    *If you know how to empty the fields when the Yes/No switch is toggled then please let me know because I am now sooo close thanks to you!

     

    if 'Is Bill To Address the same as Job Address' = true then
    Title := 'Originating Customer Name'.Title;
    'First Name' := 'Originating Customer Name'.'First Name';
    'Family Name' := 'Originating Customer Name'.'Family Name';
    'Telephone Type (Main)' := 'Originating Customer Name'.'Telephone Type (Main)'
    end

    • PAGE
    • Bill_Page
    • 3 yrs ago
    • Reported - view

    Eureka!

    Here is it in case anyone else needs it, and remember it is triggered by the Yes/No field from the field properties.

    if 'Is Bill To Address the same as Job Address' = true then
    Title := 'Originating Customer Name'.Title;
    'First Name' := 'Originating Customer Name'.'First Name';
    'Family Name' := 'Originating Customer Name'.'Family Name';
    'Telephone Type (Main)' := 'Originating Customer Name'.'Telephone Type (Main)'
    else
    if 'Is Bill To Address the same as Job Address' = false then
    Title := null;
    'First Name' := null;
    'Family Name' := null;
    'Telephone Type (Main)' := null
    end
    end

    • PAGE
    • Bill_Page
    • 3 yrs ago
    • Reported - view

    This is the full script in the Trigger on Update field in the properties of the Yes/No Choice field titled:
    Is Bill To Address the same as Job Address

    I chose the Switch field type option with [No | Yes]

    Auto fill is [Yes] but if you toggle it to [No] it clears the fields too.
    ---------------
    if 'Is Bill To Address the same as Job Address' = true then
    'Title' := 'Originating Customer Name'.Title;
    'First Name' := 'Originating Customer Name'.'First Name';
    'Family Name' := 'Originating Customer Name'.'Family Name';
    'Telephone Type (Main)' := 'Originating Customer Name'.'Telephone Type (Main)';
    'Telephone Type (Other)' := 'Originating Customer Name'.'Telephone Type (Other)';
    'Organization Name' := 'Originating Customer Name'.'Organization Name';
    'Address - Line 1' := 'Originating Customer Name'.'Address - Line 1';
    'Address - Line 2' := 'Originating Customer Name'.'Address - Line 2';
    'City/Town' := 'Originating Customer Name'.'City/Town';
    'State' := 'Originating Customer Name'.'State';
    'Zip Code' := 'Originating Customer Name'.'Zip Code';
    'Country' := 'Originating Customer Name'.'Country'
    else
    if 'Is Bill To Address the same as Job Address' = false then
    'Title' := null;
    'First Name' := null;
    'Family Name' := null;
    'Telephone Type (Main)' := null;
    'Telephone Type (Other)' := null;
    'Organization Name' := null;
    'Address - Line 1' := null;
    'Address - Line 2' := null;
    'City/Town' := null;
    'State' := null;
    'Zip Code' := null;
    'Country' := null
    end
    end
    ---------------

    • Nick
    • 3 yrs ago
    • Reported - view

    Good job Bill! 👏

Content aside

  • 3 yrs agoLast active
  • 13Replies
  • 1457Views