OFBiz from Apache

["... you mean these fans?"]

Hi,

Is there anybody here familiar with the OFBiz solution from Apache ?

I have a customer who wants to migrate thier data from FileMaker into OFBiz and I am wondering if there is anybody familiar with exporting of FileMaker's data into OFBiz solution.

After we import the historical data, I want to build additional scripts in FileMaker so when a new record is created, I export / import this new record directly into OFBiz.

Just trying to get some insight how a script like this would be designed / developed.

Thank you.

[comment]

Do we know what kind of data OFBiz can import?

[Fitch]

Looks like it has an API:

https://ci.apache.org/projects/ofbiz/site/javadocs/

That would be a good way to go when you want to update data. You may want to use it for the initial migration but worth a look. 

The FileMaker API requires FileMaker Server 17. But you'd only need that if you were using OFBiz to initiate a pull. You wouldn't need it to push from FileMaker.

["... you mean these fans?"]

We are using a MySQL database on the OFBiz side and I was able to connect FileMaker to OFBiz via the ODBC / DNS / ESS concept and I have successfully updated/edited the OFBiz record immediately as I updated/edited the FileMaker record so this part is complete, very happy.

Now I will need to figure out how to create ...

I clicked through the API link above ( thank you ) but I will have to connect with the gentleman who is helping me on the OFBiz side to guide me on the API, but from FileMaker where can find or read about how to call this API from OFBiz ?  I'll start googling API FileMaker tutorials ?

Question - Since I have successfuly connected the two databae via the ODBC / DNS, when the user creates a new record in FileMaker can I create a new record in OFBiz via a SQL call ? I guess trigger a script when a user on the FileMaker solution creates a new contact ??  I did read somewhere as I am researching, not to perfrom SQL calls through the ODBC / DNS but I can not recal were I read this ?

Any insigth I would be grateful.

Thank you.

Edited February 13, 2019 by "... you mean these fans?"

[Fitch]

Here's a pretty good overview of connecting to an external API:

https://dbservices.com/articles/integrating-filemaker-with-rest-apis/

As for creating a record, if you've got an ESS set up, you should be able to just go to a layout based on that TO and create a new record.

["... you mean these fans?"]

REST has not been establishes within OFBiz.  There had been conversation within the OFBiz community several years back but since there was no interest, the community stopped.  Strange, I don't really know but isn't REST a standard ??  and Apache, MySQL aren't they like everywhere ??  Anyway ...

I am thinking the same with regards to ESS ... meaning I will be testing tomorrow.  When we create a record in OFBiz and the serial field is empty, OFBiz will defaults to it's serial sequence and I am not sure if that value will overwrite by the primary key from FileMaker  ? Will find out tomorrow.

Once we create a new contact in FileMaker, I've checked the check box ...

 I am not sure what exactly will happen.

If the record is created in OFBiz and the primary key in OFBiz is overridden by the primary key in FileMaker, problem solved.  

If not, I am thinking that I may have to use a portal, goto 1st record and write into OFBiz primary key field the primary key from FileMaker ... ?? But since the relastionship is a mistach I may end up creating two records if I go through a portal with the above check box check.  I don't knwo what I am doing so I will be stumbling through this.

The OFBiz developer working with me is concerned with dependant table not receiveing the relative data ( primary keys ) since I will be entering directly within the OFBiz database via ESS.  But it is the same with FileMaker ... If the record get created, I will make sure the relationships are establishes and just write a script and loop through each relationship and write in the required data.

Maybe not ??

 

[Fitch]

When you create a record via relationship in FileMaker, the primary key of the parent record is automatically entered as the foreign key of the related record -- else there can be no relationship.

["... you mean these fans?"]

Yes in FileMaker.  But in OFBiz, nothing was entered ...

Here is what happened ... I created a new record in FileMaker but OFBiz PARTY_ID did not populate ?  But I could click into the field ?

Through the ODBC I typed into that field the value that was assigned to the FileMaker primary key and FileMaker primary key value I entered was visable on the OFBiz DB when we refreshed the page.

So my thinking is to run a script when the user create a new record within FileMaker that would then populate all the necessary fields intially ... anyway, 

There is one field that I do no have access too and this field is the CONTACT_MECH_ID.  Apparently without this field value established I can not commit the CONTACT_MECH_PARTY_ID and CONTACT_MECH_DATE an error pops up stating that the Child can not establish or something like that ...

So I am looking / searching if I can create a record for the CONTACT_MECH_ID directly thru an SQL create statement ?

Apparently when you create a record within OFBiz, this CONTACT_MECH_ID fields get auto established. Not sure If I can do that meaning throught the ODBC/DNS/ESS I've established and create a record using the SQL create ... researching.

Thanks

 

Edited February 17, 2019 by "... you mean these fans?"

["... you mean these fans?"]

It looks like I will be allowed to INSERT via ODBC but for some strange reason when I select the "Execute SQL" script step my OBDC drivers are not available ?  Yet they're available via the relationship graph but not within the script step? Confused ?

Suggestions ?

 

Relationship Graph:

 

Execute SQL script step:

Edited February 17, 2019 by "... you mean these fans?"

["... you mean these fans?"]

So basically my lack of understanding then and my understanding now is that once you set up the ODBC connection/driver you have access to all the tables within the MySQL databse.

At this point,  I learned that all that was needed was to create a new layout, assign the ODBC table name,  then just create my script to find, goto related record and create / edit as normal.

The MySQL or SQL calls to create or update records was not needed.

Thank you.

Mr. Ignoramus