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.
13 replies
-
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.
-
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.
-
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
Anyway, any help is really appreciated before I give up and go back to something more familiar.
-
Oops, wishe we could edit posts and this tiny area is difficult to see what is in it. Same images postes twice by mistake.
-
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.
-
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.
-
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)'
endAnd so on until all fields are copied across/match.
-
Still needing help on this guys, if anyone can figure it out - thanks!
-
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---
-
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 -
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 -
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 AddressI 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
--------------- -
Good job Bill!
Content aside
- 4 yrs agoLast active
- 13Replies
- 1459Views