We already have an custom adapter to pull in the purchase order from the PO DB, we recently have the maintenance start date and end date added the PO DB which we want to import through the adapter to FNMS. On our existing adapter we have an staging DB to process the PO records, i have added the an SQL case statement to add a maintenance column and give an value of 1 when i found dates on Maintenance start date on the PO DB.
I can see the maintenance column populated with right values (1 on where it finds the start date) and when i map the column to the maintenance object on the adapter and run it its not enabling the Maintenance on those PO's, even i cast the value to an bit on SQL i see it reflect on the staging DB but when i run it through adapter its not enabling the maintenance record.
Does any one have the same issues or suggestions to solve it?
Thanks,
Ganesh
Dec 27, 2019 11:38 AM
We have adapters built of this nature. You will need to add a value for Include Support, Maintenance or Service Agreement. You can create another field in your SQL query to case a value of Y (any value will do really) for when you have effective date populated, as such
Case When (t1.[MaintenanceDateStart] is not null and t1.[MaintenanceDateStart] <> '') Then '1'
Else NULL
END "Maintenance Flag"
Do not put a value when there is no maintenance. Any value will trigger that field to be enabled.
That should resolve the issue for you. Best of luck.
Dec 29, 2019 03:37 PM
Hi Ganesh,
I don't have a business adapter at hand for testing, but have you tried passing a string value 'true' for existing maintenance?
Best regards,
Markward
Dec 29, 2019 02:24 PM
We have adapters built of this nature. You will need to add a value for Include Support, Maintenance or Service Agreement. You can create another field in your SQL query to case a value of Y (any value will do really) for when you have effective date populated, as such
Case When (t1.[MaintenanceDateStart] is not null and t1.[MaintenanceDateStart] <> '') Then '1'
Else NULL
END "Maintenance Flag"
Do not put a value when there is no maintenance. Any value will trigger that field to be enabled.
That should resolve the issue for you. Best of luck.
Dec 29, 2019 03:37 PM
Hi @anne_watson ,
Thanks for your suggestion, i initially tried with enabling the maintenance and assigning the start dates and end dates as well on the same SQL statement as below.
case when (po.[Maintenance_start_date]) is not null then 1 end as Maintenance,
Maintenance_start_date as Maint_startdate,
Maintenance_end_date as Maint_enddate,
The above sql statements are part of the sql select statements from an staging DB, enabling the maintenance field works when i dont give the last two lines to assign the start and end dates.
Do i need to create an another adapter to assign the start and end for the PO's Maintenance has been enabled or is there a way to assign it in the same adapter.
Thanks,
Ganesh
Jan 02, 2020 01:51 PM
You should be able to do both in the same business adapter, except that I would change the SELECT on the maintenance flag:
case when (po.[Maintenance_start_date]) is not null then 1 else 0 end as Maintenance,
And when mapping this to the "Includes Maintenance" object, just assign it as a normal field value - not as a SQL field.
Kirk
Jan 02, 2020 02:42 PM
Hi Kirk,
I have to disagree with you on the 0 value. If you have any value at all, doesn't matter what it is, it takes it's presence as a yes - which is why we chose Null.
Jan 02, 2020 04:01 PM
@anne_watson - See attached example where I am using a value of 1 or 0 to set the "Purchase includes Maintenance and Support" check box in the FNMS User Interface. This is with FNMS 2019 R2 On-Premises, using the Business Adapter Studio.
Kirk
Jan 03, 2020 08:04 AM
You are on a different version. We just upgraded to 2018 R2 this year, so i will give that a go, but leaving the value blank works just as well.
Thanks for the info.
Jan 07, 2020 10:09 AM